Want to add print dialog box to worksheet.

  oo7juk 13:21 24 Oct 2007


Would like to create a box within my worksheet than once pressed triggers the print dialog box. In the past I have used vba to create a splashscreen with a command button, but that's as far as i know. Think I need to create a command butoon, but don't know how to insert or trigger print feature.

Many thanks.

  silverous 13:26 24 Oct 2007

To insert command button:

View, Toolbars, Control Toolbox.
Choose the button tool and click and drag where you want it.

To add the code, double click the button you added, (then I had to cheat on this bit and google it as I couldn't recall) you put the following code in:


You may need to take it out of "Design" mode to test. Click the blue protractor icon on the control toolbar to do that.

  VoG II 16:42 24 Oct 2007

Depends which printer dialog you want to show. Also, a good idea to let the user bail out:

Sub PRT()
Dim bResponse As Boolean
bResponse = Application.Dialogs(xlDialogPrint).Show
'Or the one below to set the printer properties
'bResponse = Application.Dialogs(xlDialogPrinterSetup).Show
If bResponse = False Then
MsgBox "User cancelled"
Exit Sub
End If
End Sub

  silverous 18:39 24 Oct 2007

The user can bailout with the above by clicking cancel?

He refers to a print dialog, not a print setup dialog?

Also if you show the print dialog, they click print, why would you then want activesheet.printout - won't you get 2 copies?

  VoG II 18:43 24 Oct 2007

If you use

bResponse = Application.Dialogs(xlDialogPrint).Show



will print an extra copy.

  oo7juk 14:32 25 Oct 2007

Many thanks guys.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

See the work of famous artists playing with toys

iPad Pro 10.5in (2017) review

Comment faire une capture d’écran sur un Mac ?