Toonies
09-07-2011, 08:37 AM
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
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