I have sheets that are OK to be viewed but not OK to be printed. Using the Workbook_BeforePrint event, I can turn off screen updating, hide the sheets not to be printed, then print out, but then the sheets are hidden. Can anyone offer a solution to this? TIA

On Friday, February 9, 2018 at 9:44:48 AM UTC, Paul Martin wrote:
I have sheets that are OK to be viewed but not OK to be printed. Using the Workbook_BeforePrint event, I can turn off screen updating, hide the sheets not to be printed, then print out, but then the sheets are hidden. Can anyone offer a solution to this? TIA

I'm using Excel 2016

On Friday, February 9, 2018 at 9:44:48 AM UTC, Paul Martin wrote:
I have sheets that are OK to be viewed but not OK to be printed. Using the
Workbook_BeforePrint event, I can turn off screen updating, hide the sheets
not to be printed, then print out, but then the sheets are hidden. Can
anyone offer a solution to this? TIA

I'm using Excel 2016

Why not make your _BeforePrint event unhide the sheets after printing?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Thanks Garry, but the _BeforePrint event executes in full before printing, so if I unhide the sheets, the sheets will print.

One option I'm contemplating is forcing the user to use a custom print button, called from Workbook_BeforePrinting, whereby Cancel = False if called from the button, or Cancel = True if called from Control P or the Excel Print commands. Can anyone confirm whether this is possible? TIA...

The technical challenge with this option is how do I determine in the code, whether it's been called from a routine I've written, or from the Excel print commands. I thought it might have been Application.Caller, but this isn't it. Any suggestions? TIA...

"Paul Martin" wrote in message
I have sheets that are OK to be viewed but not OK to be printed. Using the
Workbook_BeforePrint event, I can turn off screen updating, hide the sheets
not to be printed, then print out, but then the sheets are hidden. Can
anyone offer a solution to this? TIA

======================

Does this work for you -

' thisworkbook module

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ShowHide True
Application.OnTime Now, "'ShowHide False'"
End Sub

' normal module

Sub ShowHide(bHide As Boolean)
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet2")
If bHide Then
ws.Visible = xlSheetHidden
Else
ws.Visible = xlSheetVisible
End If
End Sub

Peter T

Thanks Peter, but looking at the code, I don't think it can possibly work as every line in it must execute before printing starts. So sheets are hidden then displayed, and so all the sheets will print.

Peter, I gave it a go, both using Now and Now + TimeValue("0:0:15") and both times I got an error indicating the macro couldn't be found.

"Paul Martin" wrote in message
Peter, I gave it a go, both using Now and Now + TimeValue("0:0:15") and
both times I got an error indicating the macro couldn't be found.

The point about calling with OnTIme is the macro won't fire until other
things have completed, such as OK'ing or cancelling the print dialog. You
could add a delay if you want but shouldn't be necessary.

About the 'couldn't be found', did you put the macro in a normal module and
include the pair of apostrophes inside the string quotes, they're needed
when including arguments.

If still can't get it to work try separate macros to show/hide, remove the
true/false argument and the apostrophes. Another thing you could try is
qualifying the macro name with its module name.

Peter T

Ah, working now. I think it was a Trust Center setting. All good now. Not perfect, but acceptable to my needs. Thanks so much...

Thanks Garry, but the _BeforePrint event executes in full before printing, so
if I unhide the sheets, the sheets will print.

You're not getting it!...

'hide not to print sheets
'call procedure to print
'_BeforePrint is NOT where to hide not to print sheets
'unhide not to print sheets

Your reply suggests you are doing everything in one procedure. BAD IDEA!(IMO)
but still doable if you hide/print/unhide so _BeforePrint isn't even in the
picture!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Thanks Garry, but the _BeforePrint event executes in full before printing,
so if I unhide the sheets, the sheets will print.

You're not getting it!...

'hide not to print sheets
'call procedure to print
'_BeforePrint is NOT where to hide not to print sheets
'unhide not to print sheets

Your reply suggests you are doing everything in one procedure. BAD IDEA!(IMO)
but still doable if you hide/print/unhide so _BeforePrint isn't even in the
picture!

Peter suggests a single procedure to show/hide the not to print sheets. I
prefer this approach wherein you pass the instruction as follows...

Const sShtsNotToPrint$ = "Sheet1,Sheet3" '//names of sheets to not print

MySub()
'...do some stuff
'Hide sheets not to be printed
Call ShowHideSheets(False)
'Print the workbook
Call PrintWorkbookProcedure
'Unhide hidden sheets
Call ShowHideSheets

Sub ShowHideSheets(Optional bVisible As Boolean = True)
Dim v
If sShtsNotToPrint = "" Then Exit Sub '//no sheets to process
For Each v In Split(sShtsNotToPrint, ",")
ActiveWorkbook.Sheets(v).Visible = bVisible
Next 'v
End Sub

...where the option services the name of the procedure in that its primary
action is 'Show' so that your code MUST pass 'False' in order to hide (ergo
'NotShow') the sheets in the not-to-print sheetnames list.

You could also make it a generic reusable procedure as follows...

Sub ShowHide_Sheets(sShtNames$, Optional bVisible As Boolean = True)
Dim v
If sShtNames = "" Then Exit Sub '//no sheets to process
For Each v In Split(sShtNames, ",")
ActiveWorkbook.Sheets(v).Visible = bVisible
Next 'v
End Sub

...so you can use it in other projects by passing it a list of sheetnames.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

"GS" wrote in message
Thanks Garry, but the _BeforePrint event executes in full before
printing, so if I unhide the sheets, the sheets will print.

You're not getting it!...

'hide not to print sheets
'call procedure to print
'_BeforePrint is NOT where to hide not to print sheets
'unhide not to print sheets

Your reply suggests you are doing everything in one procedure. BAD
IDEA!(IMO) but still doable if you hide/print/unhide so _BeforePrint isn't
even in the picture!

Hi Garry,

The OP's question is a little ambiguous. It could be interpreted as you did
if the objective were in his own routine to exclude some sheet(s) from being
printed. Indeed something along the lines you suggest would be good, and as
you say the BeforePrint doesn't come into it.

However probably no need to hide any sheets, in the print procedure simply
Worksheets(ArrayOfSheetsToPrint).PrintOut
(the array could be populated in current sheet order if/as appropriate)

I interpreted the problem being about if the user clicks Print from the
Ribbon or does Ctrl-P. BeforePrint will fire but then no control over what
the user might attempt to print. Maybe the user might be allowed to print
anything except specified sheets. For that scenario hiding sheets in the
BeforePrint and re-showing in an OnTime would be one way to handle it.
Though would need rather more that I showed in the way of error handling and
to ensure the sheets get re-shown in some other way if the OnTime failed for
any reason.

Of course nothing to stop the user taking a screenshot and printing it!

Peter T

"GS" wrote in message
Thanks Garry, but the _BeforePrint event executes in full before printing,
so if I unhide the sheets, the sheets will print.

You're not getting it!...

'hide not to print sheets
'call procedure to print
'_BeforePrint is NOT where to hide not to print sheets
'unhide not to print sheets

Your reply suggests you are doing everything in one procedure. BAD
IDEA!(IMO) but still doable if you hide/print/unhide so _BeforePrint isn't
even in the picture!

Hi Garry,
Hi Peter...

The OP's question is a little ambiguous. It could be interpreted as you did
if the objective were in his own routine to exclude some sheet(s) from being
printed. Indeed something along the lines you suggest would be good, and as
you say the BeforePrint doesn't come into it.

However probably no need to hide any sheets, in the print procedure simply
Worksheets(ArrayOfSheetsToPrint).PrintOut
(the array could be populated in current sheet order if/as appropriate)

This is the way I do it, but felt it's a bit over complicated to introduce
here.

vShtsToPrint = Split(sShtsToPrint, ",")
Worksheets(vShtsToPrint).PrintOut

I interpreted the problem being about if the user clicks Print from the
Ribbon or does Ctrl-P. BeforePrint will fire but then no control over what
the user might attempt to print. Maybe the user might be allowed to print
anything except specified sheets. For that scenario hiding sheets in the
BeforePrint and re-showing in an OnTime would be one way to handle it. Though
would need rather more that I showed in the way of error handling and to
ensure the sheets get re-shown in some other way if the OnTime failed for any
reason.

Yes, I got the same suspicion as to what the OP was doing. The OnTime approach
is a good idea in the _BeforePrint event, but I still feel it shouldn't be done
that way. Using _BeforePrint for evaluating conditionals related to the
printing itself wherein Cancel is set true accordingly is more appropriate.

IMO, if the project is that complex then it ought to have its own dedicated
procedures designed specifically for its tasks!

Of course nothing to stop the user taking a screenshot and printing it!

Peter T

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion