Question with existing print macro

[Guest]

Hi I use the following macro below to print only desired sheets,


Sub PrintButton_Click()

'to start stop screen updating (stops screen flicker)
Application.ScreenUpdating = False

Sheets("Cover").Select
Application.Dialogs(xlDialogPrint).Show

Sheets("sheet2").Select
If Range("Calcs!i1") = True Then
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If

Sheets("sheet3").Select
If Range("Calcs!f1") = True Then
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If

'etc etc

End sub


This works as required!

This macro is linked to a control button, when pressed the user sees the
standard excel print screen where they can select their printer, and then
print, no problems. If however the user decides for some reason to then press
the cancel button, I you get a run error, I was wondering if it would be
possible such if they press cancel, it goes back to the sheet called cover
instead of getting the run error?

 

[Bob Phillips]

I didn't get an error on printing, but why not just add the code to the
Beforeprint event, you can always set the Cancel argument then.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Dave Peterson]

I tried your code and clicked the cancel button. I didn't get an error because
of the cancel.

But I did get an error on the:
If Range("Calcs!i1") = True Then

This unqualified range refers to the worksheet that owns that code. And in this
case, I'm betting that it's not the Calcs worksheet.

There are a couple of different ways to fix it, but I like to qualify my ranges
this way:

Option Explicit
Sub PrintButton_Click()

Application.ScreenUpdating = False

Application.Dialogs(xlDialogPrint).Show

If Worksheets("Calcs").Range("i1") = True Then
Worksheets("Sheet2").PrintOut Copies:=1, preview:=True
End If

If Worksheets("Calcs").Range("f1") = True Then
Worksheets("sheet3").PrintOut Copies:=1, preview:=True
End If

Application.ScreenUpdating = True

End Sub

Note that you don't have to select the worksheet to print it.

And I added preview:=true for testing.

By the way, the unqualified range would refer to the activesheet in a general
module, but this code looks like it's behind the worksheet.

 

[Guest]

Hi Guys,

You had me pondering there, anyway, I have done some checks printing, and I
found out I only get the error if printing to MS office image document writer
which I was using to test the program, if I print to a printer you can cancel
and its fine.

Any thoughts on stopping the error if used with this type of printer as some
of the guys will be using this printer on site to store the result
electronically?

Dave, thanks for the code, that is simpler and cuts down on a few lines
which is my aim now. Plan 1 was to get a working project then once I've
grasped it to try and cut down some of the code text.

Thanks to both of you.

 

[Dave Peterson]

Maybe trap for the error???

On Error Resume Next
Application.Dialogs(xlDialogPrint).Show
If Err.Number <> 0 Then
'an error occurred
Err.Clear
'do what you want
exit sub '?
End If

Hi Guys,

You had me pondering there, anyway, I have done some checks printing, and I
found out I only get the error if printing to MS office image document writer
which I was using to test the program, if I print to a printer you can cancel
and its fine.

Any thoughts on stopping the error if used with this type of printer as some
of the guys will be using this printer on site to store the result
electronically?

Dave, thanks for the code, that is simpler and cuts down on a few lines
which is my aim now. Plan 1 was to get a working project then once I've
grasped it to try and cut down some of the code text.

Thanks to both of you.

 

[Guest]

Hi Dave,

Works a treat, thank you!