msgbox "X" button

[rickblunt]

Greeting, tried searching for this, but I can't find the answer - probably because I am not asking the question correctly... What is the name for the little "x" close button in the top right of a msgbox?

Just ran across something that either I have never noticed before or perhaps I just have never clicked on it before (though I am sure that I must have by now :/) If I run the little sub below and I decide that I don't want to run the macro, if I click on the "x" the macro runs anyway instead of closing out the msgbox. I know that I could just add a cancel or no button to the msgbox, but that still would not prevent the issue if the user clicks the x. I guess I always thought that the "x" was a default cancel event but I guess not. What is the syntax that I need to add so that clicking the "x" stops the macro and closes the sub?

Thanks

Sub EmergencyLightClearForm()


MsgBox "Are you sure that you want to clear the data from this report?  This cannot be undone"


Worksheets("Emergency Lighting Inspection").Activate
    Range("E4:K17").Select
    Selection.ClearContents
    Range("P4:V61").Select
    Selection.ClearContents
    Range("Z4:AF17").Select
    Selection.ClearContents
    Range("AJ4:AP17").Select
    Selection.ClearContents
    Range("A1").Select
    
End Sub

 

[Beyond_avarice]

Because you are not using a message box that allows you to return a value other than 1(one) and you are not creating an if statement that will help you exit the subroutine.

The "vbOKCancel" will make "Cancel"(or "x") =2 while "OK"=1

Sub EmergencyLightClearForm()
 
Dim ans As Single
 
ans = MsgBox("Are you sure that you want to clear the data from this report?  This cannot be undone", vbOKCancel)

If ans = 1 Then
    Worksheets("Emergency Lighting Inspection").Activate
        Range("E4:K17").Select
        Selection.ClearContents
        Range("P4:V61").Select
        Selection.ClearContents
        Range("Z4:AF17").Select
        Selection.ClearContents
        Range("AJ4:AP17").Select
        Selection.ClearContents
        Range("A1").Select
End If
    
End Sub

 

[Eric W]

Maybe:

Sub EmergencyLightClearForm()

    If MsgBox("Are you sure that you want to clear the data from this report?  This cannot be undone", _
                vbOKCancel, "Warning!") = vbOK Then
    
        Worksheets("Emergency Lighting Inspection").Activate
        Range("E4:K17, P4:V61, Z4:AF17, AJ4:AP17").ClearContents
        Range("A1").Select
    End If
    
End Sub

 

[rickblunt]

Thank you both for these improvements to my code, and more importantly the knowledge that you have shared with me, I really appreciate the tutoring. It makes sense about me not making it an "if" statement being the problem - Thanks again.