Want to add print dialog box to worksheet.

  oo7juk 13:21 24 Oct 07


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 07

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 07

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 07

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 07

If you use

bResponse = Application.Dialogs(xlDialogPrint).Show



will print an extra copy.

  oo7juk 14:32 25 Oct 07

Many thanks guys.

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

Sniper Elite 4 review: Headshotting Nazis has never felt so good

1995-2015: How technology has changed the world in 20 years

The Best Design, Illustration, Animation and VFX Awards of 2017

WWDC 2017 dates: How to get WWDC 2017 tickets, when is WWDC 2017 and more details announced