Consulting

Results 1 to 7 of 7

Thread: Solved: Vba debugging Run-time error '1044':

  1. #1
    VBAX Regular
    Joined
    Feb 2011
    Posts
    34
    Location

    Solved: Vba debugging Run-time error '1044':

    hi and thanks for looking at my post.

    Excel 2003

    I have the following code that is causing an issue in a protected sheet

    when i run the Combobox it comes up with the following error mesage

    Run-time error '1004':
    Application-defined or object-defined error

    this part of the code is highlighted in Yellow

    [VBA]If Cells(4, Ac + 2) >= sDt And Cells(4, Ac + 2) <= eDt Then[/VBA]

    here is the main code below

    [VBA]
    Private Sub CommandButton1_Click()
    Dim Rng As Range, Dn As Range
    Dim sDt As Date
    Dim eDt As Date
    Dim Ac As Integer
    Dim col As Integer
    Set Rng = Range(Range("B5"), Range("B" & Rows.Count).End(xlUp))
    sDt = ComboBox1
    eDt = ComboBox2
    Select Case True
    Case Is = holidayButton1: col = 43
    Case Is = sickLeaveButton3: col = 53
    Case Is = otherOptionButton4: col = 37
    End Select
    For Each Dn In Rng
    If Dn = nameBox1 Then
    For Ac = 1 To 366 ' Change to 366
    If Cells(4, Ac + 2) >= sDt And Cells(4, Ac + 2) <= eDt Then
    If Weekday(Cells(4, Ac + 2), vbMonday) < 6 Then
    If IsError(Application.Match(Cells(4, Ac + 2), Range("PUBLICHOLIDAY"), 0)) Then
    Dn.Offset(, Ac).Interior.ColorIndex = col
    End If
    End If
    End If
    Next Ac
    End If
    Next Dn
    Unload Me
    End Sub[/VBA]

    if I end the Debugging, the form then continues work correctly until the next time I want to use it.

    I look forward to any help

    Toonies

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    24,994
    Location
    Have you tried unprotecting the sheet at the start of the code, unprotect it at the end?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Regular
    Joined
    Feb 2011
    Posts
    34
    Location
    Hi i have tried unprotecting the sheet but i still get the same error

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    24,994
    Location
    Can you post the workbook, and tell us the password.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Regular
    Joined
    Feb 2011
    Posts
    34
    Location
    No problem

    the password is abc

    There is Password VBA in the following

    Sheet1(January-June)

    [VBA]Private Sub worksheet_calculate()
    ActiveSheet.Unprotect "abc"
    With ActiveSheet
    .Columns("BJ").EntireColumn.Hidden = (.Range("BI1").Value = "")
    ActiveSheet.Protect "abc"
    End With
    End Sub[/VBA]

    ThisWorksbook

    [VBA]Option Explicit
    Private Sub Workbook_Open()
    Dim wks As Worksheet
    For Each wks In ThisWorkbook.Worksheets
    wks.Protect Password:="abc", UserInterfaceOnly:=True
    Next wks
    End Sub[/VBA]

    The offending code is found in UserForm1

    many thanks for looking
    Attached Files Attached Files

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    24,994
    Location
    You problem is that AC is looping from 1 to 366, but you only have 256 columns.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    VBAX Regular
    Joined
    Feb 2011
    Posts
    34
    Location
    Thanks xld for pointing out my mistake DOH!

    Works fine, well at least that part.

    another few gremlins to try and sort out first but this thread is Solved

    Many thanks


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •