98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Preventing Users to print certain worksheets | Excel forum
Preventing Users to print certain worksheets | Excel forum
Resolved · Medium Priority · Version 2010
Thu 21 Apr 2016, 11:02Delegate Matthew said...
Matthew has attended:
Excel VBA Intro Intermediate course
Preventing Users to print certain worksheets
I am currently help redesign a report that my employer users there are certain sheets within the workbook that shouldn't be printed.
This is the code I am using
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim DoNotPrintList As String
Dim DisablePrint As Variant
Dim sht As Worksheet
DisablePrint = Array("Cost codes", "Cost Report")
For x = LBound(DisablePrint) To UBound(DisablePrint)
DoNotPrintList = DoNotPrintList & DisablePrint(x) & ";"
Next x
For Each sht In ThisWorkbook.Windows(1).SelectedSheets
If DoNotPrintList Like "*" & sht.Name & ";*" Then
Cancel = True
Exit For
End If
Next sht
End Sub
The issue I am having is that when the user tries to print the sheets on there own the code prevents them from printing but if they do print entire workbook they print.
Is there a way of stopping the sheets from being printed when print entire workbook is used?
Thanks
Matt
Thu 21 Apr 2016, 13:49Delegate Matthew said...
RE: Preventing Users to print certain worksheets
Or is there a way of hiding the sheets and hiding the sheets with a "Workbook_BeforePrint"?
Thu 21 Apr 2016, 16:20Delegate Matthew said...
RE: Preventing Users to print certain worksheets
Update I have sort of solved the issue with the code below
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Application.EnableEvents = False
Application.ScreenUpdating = False
Sheets(Array("Cost Report", "Cost codes")).Select
Sheets("Cost codes").Activate
ActiveWindow.SelectedSheets.Visible = False
PrintOut
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
It successfully hides the worksheets and doesn't print them but now my issue is un-hiding them again after printing. I have tried the code below but adding the lines to unhide them after the PrintOut Command still causes them to print.
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Application.EnableEvents = False
Application.ScreenUpdating = False
Sheets(Array("Cost Report", "Cost codes")).Select
Sheets("Cost codes").Activate
ActiveWindow.SelectedSheets.Visible = False
PrintOut
'Sheets("Cost Report").Visible = True
'Sheets("Cost codes").Visible = True
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Any help would be appreciated in solving my problem.
Thanks
Matt
Mon 25 Apr 2016, 14:26Trainer Jens said...
RE: Preventing Users to print certain worksheets
Hi Matthew,
Thank you for the forum question.
This is not an easy one.
The moment you are in a before_print the worksheets will print when they get visible again. I have been doing a lot of tests but haven't found the right way yet. I will come back to you later.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Mon 25 Apr 2016, 19:44Delegate Matthew said...
RE: Preventing Users to print certain worksheets
Jens,
Thank you for getting back to me. I have solved the issue now but the code I have written only prints entire workbook even if I select active sheet. I will post the code tomorrow when I am in work.
Matt
Tue 26 Apr 2016, 09:20Delegate Matthew said...
RE: Preventing Users to print certain worksheets
Jens,
This code works it hides and makes the two workbooks visible again after printing
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
Application.EnableEvents = False
Application.ScreenUpdating = False
Sheets(Array("Cost Report", "Cost codes")).Select
Sheets("Cost codes").Activate
ActiveWindow.SelectedSheets.Visible = False
PrintOut
Sheets("Cost Report").Visible = True
Sheets("Cost codes").Visible = True
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
I just have the issue now of it always printing entire workbook all the time even if I select print active sheet. Is there anyway I can solve this?
Thanks,
Matt
Thu 28 Apr 2016, 11:38Trainer Jens said...
RE: Preventing Users to print certain worksheets
Hi Matthew,
This is a very tricky code. It sounds so easy but it is so difficult to get it right.
The code below works for me, but you will need to hide two sheets.
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Application.EnableEvents = False
ThisWorkbook.Worksheets("sheet3").Visible = xlVeryHidden
Application.Dialogs(xlDialogPrint).Show
ThisWorkbook.Worksheets("sheet3").Visible = xlSheetVisible
Application.DisplayAlerts = True
Application.EnableEvents = True
Cancel = True
End Sub
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Find cells that match a formatIn Excel you may wish to find cells that contain a specific formatting such s colour. |
