Add a Confirmation Message Box to a macro?

[n2lectual]

I have a macro that needs a confirmation before running with a YES and NO button.

User clicks macro button, Message box ask "ARE YOU SURE?"
If yes macro runs, if no message box and macro exits.

What code do I need to insert and where within the macro?
Thanks

 

[Jonmo1]

response = MsgBox("Are You Sure?",vbyesno)
 
If response = vbno then 
    Msgbox("Macro Ending")
    exit sub
End If
 
rest of code here.

 

[lenze]

rspn = MsgBox("Are you Sure?", vbYesNo)
If rspn = vbNo Then Exit Sub
'code

lenze

 

[n2lectual]

Works great. Thank you both.

 

[Eruantano]

Does this not work for showing userforms from button click events?
I have a button on a worksheet and I want to provide a confirmation MsgBox to ensure that the user wishes to load data to that specific worksheet. However, whether I press YES or NO, the userform still shows. Can someone clarify this for me?

Thanks.
Joe.

Private Sub CommandButton1_Click()
 
    Dim Ans As Boolean
 
    Ans = MsgBox("Do you wish to add a new patient to the " & _
        ActiveSheet.Name & " list?", vbYesNo, "Add a new patient?")
        
    If Ans = vbNo Then Exit Sub
    
    frmNewPat.Show False
    
End Sub

 

[VoG]

Dim Ans as Integer (not Boolean)

 

[Eruantano]

Dim Ans as Integer (not Boolean)

Oh! Ok, thanks! I think I got that from somewhere online. Thanks for the correction.

Joe.

 

[VoG]

Sorry for my rather terse response above but I was about to dash off.

You can also use

Dim ans As VbMsgBoxResult

which may make the code more understandable when you revisit it sometime in the future. But declaring ans as an Integer works just as well.

 

[Eruantano]

Sorry for my rather terse response above but I was about to dash off.

You can also use

Dim ans As VbMsgBoxResult

which may make the code more understandable when you revisit it sometime in the future. But declaring ans as an Integer works just as well.

That is valuable information! Thanks so much.