vba Change Macro not firing consistantly

[Jesienouski]

First post, so be kind. I have a change macro within a large workbook that is not firing consistantly. The change will fire the first time the workbook is opened and the change takes place, but then stops working after that. Does anyone know what is going on? I can send the workbook if needed, but it is almost 6 megs. I'm working in '07 if that helps too.

 

[RoryA]

Do you have a form called frmCalendar4?

 

[Jesienouski]

Yes, and it's identical to frmCalendar3 which is working except for the range it looks at.

Private Sub Calendar4_Click()
    Sheet28.Select
    Sheet28.Range("H9").Value = Calendar4.Value
    Unload Me
    Sheet28.Range("H9").Select
End Sub
Private Sub cmdClose_Click()
    Unload Me
    Sheet28.Range("H9").Select
End Sub
Private Sub UserForm_Initialize()
    If IsDate(Range("H9").Value) Then
        Calendar4.Value = DateValue(ActiveCell.Value)
    Else
        Calendar4.Value = Date
    End If
End Sub

 

[RoryA]

Comment out the On Error Goto err_handler line in the Change event code (just put an apostrophe in front of it) then change the value in H9. When the error message pops up, press f8 to step through the code line by line until an error occurs and tell me which line it occurs on.

 

[Jesienouski]

It errors on the UserForm_Initialize Sub after the Else, it runs down and when it gets to Calendar4.Value = Date it errors. What's weird is that is the exact same code as the other calendars I have

Private Sub UserForm_Initialize()
    If IsDate(Range("H9").Value) Then
        Calendar4.Value = DateValue(ActiveCell.Value)
    Else
        Calendar4.Value = Date
    End If
End Sub

 

[RoryA]

Do you have a calendar control on that form that is called Calendar4? (doesn't sound like it)

 

[Jesienouski]

WOW! I don't know how that fell off. Simple trouble shooting, thanks!