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

Dell XPS 13 9370 (2018) review

No need to scan sketches into your computer with Moleskine's new smart pen

How to use 3D Touch on iPhone

Comment importer des contacts d’un iPhone à un autre iPhone ?