(Solved) – VBA Excel Getting Run-time error 91 once UserForm is unloaded

(solved)-–-vba-excel-getting-run-time-error-91-once-userform-is-unloaded

I have created a UserForm however once I click CommandButton1 (ok) or CommandButton2 (cancel) I always get an error.

Error Message

Run-time error ’91’:
Object variable or With block variable not set

Debugging

I have checked my code to the best of my knowledge. I searched the ‘similar questions’ when typing this question but nothing. I have also Debug-Compile without errors. I’m at a lose..

Code

Please note: all code below is within the UserForm

Option Explicit
Private FirstSunday As Date
Private SecondSunday As Date


Private Sub CheckBox1_Click()
    If CheckBox1.value = True Then: CheckBox2.value = False
End Sub

Private Sub CheckBox2_Click()
    If CheckBox2.value = True Then: CheckBox1.value = False
End Sub

Private Sub CommandButton1_Click()

    If CheckBox1.value = True Then
       MsgBox "First Sunday"  ' testing - variable to be used in SAP macro
       Unload Me
    ElseIf CheckBox2.value = True Then
       MsgBox "Second Sunday"  ' testing - variable to be used in SAP macro
       Unload Me
    Else
        MsgBox "No date selected. Please select a date or press 'Cancel'.", 0   48   0, "Error - No selection"
    End If

End Sub

Private Sub CommandButton2_Click()
    Unload Me
End Sub

Private Sub UserForm_Initialize()

    GetSundayDates

    With frmMthEndDate
        With .Question
            .Caption = "Please select the last Sunday of " & Format(FirstSunday, "MMMM") & " Month-End then click the 'OK' button."
            .Font.Size = 8
        End With
        .CommandButton1.Caption = "OK"
        .CommandButton2.Caption = "Cancel"
    End With

    With CheckBox1
        .Caption = FirstSunday
        .Font.Size = 8
    End With
    With CheckBox2
        .Caption = SecondSunday
        .Font.Size = 8
    End With

    Me.Show

End Sub

Private Sub GetSundayDates()

    Dim WeekdayInteger As Long
    WeekdayInteger = Weekday(DateSerial(Year(Date), Month(Date), 0), vbMonday)

    Dim MinusDays As Long
    Select Case WeekdayInteger
        Case 1:  MinusDays = -1 ' Mon
        Case 2:  MinusDays = -2 ' Tue
        Case 3:  MinusDays = -3 ' Wed
        Case 4:  MinusDays = -4 ' Thu
        Case 5:  MinusDays = -5 ' Fri
        Case 6:  MinusDays = -6 ' Sat
        Case 7:  MinusDays = 0 ' Sun
    End Select

    FirstSunday = DateSerial(Year(Date), Month(Date), MinusDays - 7)
    SecondSunday = DateSerial(Year(Date), Month(Date), MinusDays)

End Sub

edit adding sub calling form

Sub TestingForm()

    frmMthEndDate.Show

End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *