VBA print setup
 

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 fileprint on the excel menu? I want to have those
options when printing; like number of copies, printer to print to, etc.
thanks.

VBA print setup
 

Probably the second one

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


--
Regards,
Tom Ogilvy


"Jacob" wrote:

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 fileprint on the excel menu? I want to have those
options when printing; like number of copies, printer to print to, etc.
thanks.

VBA print setup
 

That second line did it! Thanks!


Tom Ogilvy wrote:
Probably the second one

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


--
Regards,
Tom Ogilvy


"Jacob" wrote:

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 fileprint on the excel menu? I want to have those
options when printing; like number of copies, printer to print to, etc.
thanks.