Excel VBA: Confirmation on Pressing CommandButton

Question

Upon pressing my CommandButton, I would like to have a pop-up that asks "These changes cannot be undone. It is advised to save a copy before proceeding. Do you wish to proceed?"

And I want to have three options:

1. Yes - pop-up window is closed and CommandButton Macro is executed
2. No - This closes the pop-up window and changes nothing
3. Save - closes pop-up window and opens "Save As" (macro is not executed)

I don't really know where to start with this. Could you please give me a hand?

Thank you very much indeed.

Answer 1

You can use a message box, but that is somewhat limited. You can rephrase the question slightly to use the vbYesNoCancel buttons, since Save As is not an optional button on Message Box.

Then you can work with the result of the message box button-click:

Dim mbResult as Integer
mbResult = MsgBox("These changes cannot be undone. Would you like to save a copy before proceeding?", _
 vbYesNoCancel)

Select Case mbResult
    Case vbYes
        'Modify as needed, this is a simple example with no error handling:
        With ActiveWorkbook
            If Not .Saved Then .SaveAs Application.GetSaveAsFilename()
        End With
    Case vbNo
        ' Do nothing and allow the macro to run

    Case vbCancel
        ' Do NOT allow the macro to run
        Exit Sub

End Select

Answer 2

I suggest you put code at the top of your macro to ask this question and respond to the answer.

This would look something like:

Sub YourMacro()

  if MsgBox("These changes cannot be undone. It is advised to save a copy before proceeding. Do you wish to proceed?", vbYesNo + vbQuestion) = vbNo then
    exit sub
  end if

  ... the rest of your macro.

Note that this will not give the user the Save option. You can't do that with a standard MsgBox. If you want to do that, you will need to create your own userform, show that instead of the MsgBox and respond to what button in the Userform the user pushed. That is a lot more work for you than just using a MsgBox, but if you want your UI to be fancy, it may be worth it.