VBA print setup

J

Jacob

I have a spreadsheet run entirely from a userform. I currently use the
following code to print out form by pressing a print button on the form
itself:

Private Sub PrintButton_Click()
' keybd_event VK_SNAPSHOT, 0, 0, 0
DoEvents
keybd_event VK_LMENU, 0, _
KEYEVENTF_EXTENDEDKEY, 0 ' key down
keybd_event VK_SNAPSHOT, 0, _
KEYEVENTF_EXTENDEDKEY, 0
keybd_event VK_SNAPSHOT, 0, _
KEYEVENTF_EXTENDEDKEY + KEYEVENTF_KEYUP, 0
keybd_event VK_LMENU, 0, _
KEYEVENTF_EXTENDEDKEY + KEYEVENTF_KEYUP, 0
DoEvents
Workbooks.Add
Application.Wait Now + TimeValue("00:00:06")
ActiveSheet.PasteSpecial Format:="Bitmap", _
Link:=False, DisplayAsIcon:=False
With ActiveSheet.PageSetup
.RightFooter = Me.Caption & ": &D Page &P/&N"
.PrintGridlines = False
.Orientation = xlLandscape
.PaperSize = xlPaperA4
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
ActiveSheet.Range("A1").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
ActiveWorkbook.Close False
End Sub


I want to know if there is a way to bring up the print dialog that pops
up when you go to file>print on the excel menu? I want to have those
options when printing; like number of copies, printer to print to, etc.
thanks.
 
T

Tom Ogilvy

Probably the second one

Application.Dialogs(xlDialogPrinterSetup).Show
APPLICATION.Dialogs(xlDialogPrint).Show
 
J

Jacob

That second line did it! Thanks!


Tom said:
Probably the second one

Application.Dialogs(xlDialogPrinterSetup).Show
APPLICATION.Dialogs(xlDialogPrint).Show
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top