PDA

View Full Version : Print Macro



Odyrus
06-02-2011, 07:49 AM
Good day all,

I have a print macro that works wonderfully except when my dialog box appears to give the user a printer option and he or she clicks cancel the job prints regardless. How do I get the job to not print when the user cancels out of the printer dialog box?

Sub PrintReports()


'Turn screen updating off
Application.ScreenUpdating = False

'select printer
Application.Dialogs(xlDialogPrinterSetup).Show

'unhide sheets
Dim wsSheet As Worksheet
For Each wsSheet In ActiveWorkbook.Worksheets
wsSheet.Visible = xlSheetVisible
Next wsSheet

'Print workbook
ActiveWorkbook.PrintOut Copies:=1, Collate:=True

'Turn screen updating back on
Application.ScreenUpdating = True
End Sub



As always, much gratitude!

Cheers!

Chabu
06-02-2011, 09:13 AM
The dialog returns a boolean that is False if the user clicks the cancel button and True if OK.
Dim x As Boolean
'select printer
x = Application.Dialogs(xlDialogPrinterSetup).Show

put the rest of the code in an IF block

Odyrus
06-03-2011, 05:12 AM
That makes sense, thanks.

My only problem is placing the syntax properly. :)

Odyrus
06-06-2011, 04:50 AM
Bump.

If possible, I'd appreciate assistance with the correct syntax for my dilemma. Help much welcomed!

Odyrus
06-06-2011, 06:14 AM
Since I can't delete a post I'll redact it; I think I have it figured. Thanks!

CatDaddy
06-06-2011, 10:52 AM
Sub PrintReports()

'Turn screen updating off
Application.ScreenUpdating = False


Dim x As Boolean
'select printer
x = Application.Dialogs(xlDialogPrinterSetup).Show

If x = True

'unhide sheets
Dim wsSheet As Worksheet
For Each wsSheet In ActiveWorkbook.Worksheets
wsSheet.Visible = xlSheetVisible
Next wsSheet

'Print workbook
ActiveWorkbook.PrintOut Copies:=1, Collate:=True

End If

'Turn screen updating back on
Application.ScreenUpdating = True
End Sub