Add message box before committing Macro

[Livin404]

Greetings, I have a macro which works great in deleting contents. I thought I should have a message box asking the user are they sure before they commit to a major task. The message will read " This action will clear all PERSONNEL data from this X-Man". The clear macro I am currently using is:

Sub XMan()
With ActiveSheet
 With Range("A3:AB3", Range("A" & Rows.Count).End(xlUp)).ClearContents
End With
End With
End Sub

 

[gd6noob]

You could try this

If answer = vbYes Then
  MsgBox "This action will clear all PERSONNEL data from this X-Man"
Else
  MsgBox "No"
End If

 

[Livin404]

You could try this

If answer = vbYes Then
  MsgBox "This action will clear all PERSONNEL data from this X-Man"
Else
  MsgBox "No"
End If

I did try it. It did clear it out, but then the "NO" message came up. Perhaps I should have a Yes which will clear it and a No will just cancel the request out. Thank you ever so much!

Sub Clear_XMan()
With ActiveSheet
With Range("A3:AB3", Range("A" & Rows.Count).End(xlUp)).ClearContents
If answer = vbYes Then
  MsgBox "This action will clear all PERSONNEL data from this X-Man"
Else
  MsgBox "No"
End If
End With
End With
End Sub

 

[Livin404]

Thank for getting me in the right direction. The macro with some help we ended up with is: And it seems to work.

Sub XMan()
Dim Answer As VbMsgBoxResult

Answer = MsgBox("This action will clear all PERSONNEL data from this X-Man" & vbNewLine & "Are you sure you want to do it?", vbYesNo, "WARNING")

If Answer = vbNo Then

        Exit Sub
Else

        With ActiveSheet
            .Range("A3:AB3", Range("A" & Rows.Count).End(xlUp)).ClearContents
        End With
End If
End Sub

 

[Domenic]

Try...

Sub XMan()

    Dim ans As VbMsgBoxResult
    ans = MsgBox("This action will clear all PERSONNEL data from" & vbCrLf & "this X-Man, continue?", vbQuestion + vbYesNo, "Continue?")

    If ans = vbNo Then Exit Sub
    
    With ActiveSheet
       .Range("A3:AB3", .Range("A" & .Rows.Count).End(xlUp)).ClearContents
    End With
    
End Sub

By the way, notice that the references within the With/End With statement are preceded with a dot ( . ) so that refer to ActiveSheet.

Hope this helps!