MsgBox to "Print" or "Preview"

Question

I want a Message Box that prompts the user to either “Print” or “ Print Preview”. I have used the following “yes or no” code in other macros but wonder if it can be modified to perform this action?

Thanks in advance for any help you may provide.

Tom

Dim Response As VbMsgBoxResult

   Response = MsgBox("Do you want to print or preview all the worksheets?", vbQuestion + vbYesNo)

   If Response = vbNo Then Exit Sub

Answer 1

Steps for creating the userform:

1. In the VBE choose Insert, UserForm

2. In the Properties window on the lower left (if you don't see it choose View, Properties Window) change the Caption property to a name you want to display at the top of the dialog box = userform, optionally, change the Name property to something like frmPrint (no spaces in the name property)

3. Click on the userform in the right hand area and the toolbox should be visible. 
4. Click the Button tool  and then click on the user form where you want one of the buttons.

5. With the button selected, change its Name property to btnPrint, change its Caption property to Print.

6. Repeat step 5 for a Print Preview button and a Cancel button
7. Double-click the Print button and add the code the following code to the form's module

Private Sub cmdCancel_Click()

    Unload Me

End Sub

Private Sub cmdPreview_Click()

    Unload Me

    Selection.PrintPreview

End Sub

Private Sub cmdPrint_Click()

    Selection.PrintOut

    Unload Me

End Sub

You will add one line in your regular module to launch the dialog box:

frmPrint.Show

---

If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers, Shane Devenshire

Answer 2

Hi,

To change the button captions you would have to use a userform with 3 buttons on with the appropriate caption and  coding.

The code would be no different than shown above.

Answer 3

Hi Mike,

Thanks for your reply. This doesn't work quite right. 

 I was hoping to modify the "yes" and "no" buttons to read "Print" or "Preview" along with the code to perform the action based on the user selection. 

Thanks,

Tom

Answer 4

Here is a slightly fancier version using the MsgBox command which allows the user to pick three choices, Print, Preview, Cancel

Sub test()

    Dim myMsg As Integer

    myMsg = MsgBox("If you want to print click Yes, to preview click No, otherwise click Cancel", vbQuestion + vbYesNoCancel)

    If myMsg = vbYes Then

        Selection.PrintOut

    ElseIf myMsg = vbNo Then

        Selection.PrintPreview

    Else

        Exit Sub

    End If

End Sub

Technically one should use a user form if you want to control what the buttons actually say, for example Print, Print Preview, Cancel.

---

If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers, Shane Devenshire

Answer 5

Hi,

How about something like this

Dim Response As VbMsgBoxResult

Response = MsgBox("Do you want to print or preview all the worksheets?", _

vbQuestion + vbYesNo)

If Response = vbNo Then Exit Sub

ActiveWindow.ActiveSheet.PrintPreview

Response = MsgBox("Do you want to print the worksheets?", _

vbQuestion + vbYesNo)

If Response = vbNo Then Exit Sub

ActiveWindow.ActiveSheet.PrintOut