Excel VBA: Cancel a checkbox_change event? 1

[NuJoizey]

A user mistakenly clicks a checkbox from False to True. I want a message box to pop up saying "Are you sure?" If user responds "NO" then I want to exit out of the routine and set the checkbox back to False. But the problem is that when I do this, the event is re-fired and AddCols() procedure keeps getting called - but I don't want this to happen. So then, how are you supposed to capture a mistake by user and roll back the action? This is driving me crazy!

Private Sub chk_Change()

    If Me.chk.Value = True And Range("D14") > 1 Then
        'delete everything from column e to the last column where there is data entry
        If MsgBox("Are you sure?") = vbYes Then    
            Call DeleteCols()
        Else
            'reset the checkbox
            Me.chk.Value = False 
            Exit Sub
        End If
    Else
        'I DON'T WANT THIS TO BE CALLED IF THE USER ANSWERS "NO" TO "ARE YOU SURE"
        Call AddCols()
    End If

End Sub

 

[NuJoizey]

I tried:

Application.EnableEvents = False
     Me.chk.Value = False 
Application.EnableEvents = True

but that didn't work. arrgh

 

[DaveInIowa]

Try this. Note that your original code would never call DeleteCols as the default button on MsgBox is vbOK.

Private Sub chk_Change()
    Static isResetting As Boolean
    Dim response As VbMsgBoxResult
    
    If (isResetting) Then
        isResetting = False
        Exit Sub
    End If

    If Me.chk.Value = True And Range("D14") > 1 Then
    
        response = MsgBox("Are you sure?", vbQuestion + vbYesNo, "Delete Columns")
    
        'delete everything from column e to the last column where there is data entry
        If (response = vbYes) Then
            Call DeleteCols
        Else
            'reset the checkbox
            isResetting = True
            Me.chk.Value = False
        End If
    Else
        Call AddCols
    End If

End Sub

 

[NuJoizey]

ah, so the Static declaration is the key. Much obliged!