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

[Toonies]

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

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

here is the main code below


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

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

[Bob Phillips]

Have you tried unprotecting the sheet at the start of the code, unprotect it at the end?

[Toonies]

Hi i have tried unprotecting the sheet but i still get the same error

[Bob Phillips]

Can you post the workbook, and tell us the password.

[Toonies]

No problem

the password is abc

There is Password VBA in the following

Sheet1(January-June)

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

ThisWorksbook

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

The offending code is found in UserForm1

many thanks for looking

[Bob Phillips]

You problem is that AC is looping from 1 to 366, but you only have 256 columns.

[Toonies]

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

:beerchug: