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:

Yes - pop-up window is closed and CommandButton Macro is executed
No - This closes the pop-up window and changes nothing
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.

Use VbMsgBoxStyle.vbYesNoCancel or create new form. If you are on Windows Vista or higher you could use fancy system messageboxes, but that's rather advanced topic. — Mike
– Mike, Commented May 30, 2013 at 14:48
I put this just after my sub code begins. This yields following error: "Invalid use of property". How do I need to use this code? Thanks Mike. — jcv
– jcv, Commented May 30, 2013 at 14:57

David Zemens · Accepted Answer · 2013-05-30 15:12:55Z

23

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

edited May 30, 2013 at 15:12

answered May 30, 2013 at 15:00

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dick Kusleika Over a year ago

+1 Also add a If Not ActiveWorkbook.Saved Then so that you don't even ask if the workbook is already saved.

GTG · Accepted Answer · 2013-05-30 14:56:29Z

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.

Collectives™ on Stack Overflow

Excel VBA: Confirmation on Pressing CommandButton

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related