Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cancel printing a worksheet
Having a similar problem where I never wanted a selected sheet to
print. The solutions posted at best still printed something when the sheet that I did not want to print was active. I used the following code to alway block the printing of "Sheet1" in the example where the workbook has three (3) sheets: Private Sub Workbook_BeforePrint(Cancel As Boolean) If ActiveSheet.Name = "Sheet1" Then MsgBox "Print Error, Unable to Print Sheet1", vbCritical Cancel = True Else If ActiveSheet.Name = "Sheet2" Or ActiveSheet.Name = "Sheet3" Then Application.DisplayAlerts = False Application.EnableEvents = False ThisWorkbook.Worksheets("Sheet1").Visible = xlVeryHidden Application.Dialogs(xlDialogPrint).Show ThisWorkbook.Worksheets("Sheet1").Visible = xlSheetVisible Application.DisplayAlerts = True Application.EnableEvents = True Cancel = True End If End If End Sub The down side of this solution is that one must list all other workbook sheets in the line following the "Else" statement. The "Cancel" command near the end of the code was necessary to prevent two (2) sheets from printing. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cancel printing a worksheet
That really doesn't stop them from printing sheet1. They can have Sheet2 active
and print the entire workbook. If this were important to me, then I'd turn off all printing with code like this: Option Explicit Private Sub Workbook_BeforePrint(Cancel As Boolean) Cancel = True MsgBox "Please use the button to print" End Sub Then give the users a way to print -- but only what you want to allow. Option Explicit Sub YourPrintMacroHere() if lcase(activesheet.codename) = lcase("Sheet1") then msgbox "can't be printed" exit sub end if 'right before you print application.enableevents = false activesheet.printout application.enableevents = true End Sub Then put a button from the Forms toolbar on each sheet that's ok to print. And assign this macro to each button. === Instead of relying on the sheet name on the tab (which is easier for the user to change), I used the codename for the sheet--which is more difficult for the typical user. But all this dies if the user disables macros! "Johnny W." wrote: Having a similar problem where I never wanted a selected sheet to print. The solutions posted at best still printed something when the sheet that I did not want to print was active. I used the following code to alway block the printing of "Sheet1" in the example where the workbook has three (3) sheets: Private Sub Workbook_BeforePrint(Cancel As Boolean) If ActiveSheet.Name = "Sheet1" Then MsgBox "Print Error, Unable to Print Sheet1", vbCritical Cancel = True Else If ActiveSheet.Name = "Sheet2" Or ActiveSheet.Name = "Sheet3" Then Application.DisplayAlerts = False Application.EnableEvents = False ThisWorkbook.Worksheets("Sheet1").Visible = xlVeryHidden Application.Dialogs(xlDialogPrint).Show ThisWorkbook.Worksheets("Sheet1").Visible = xlSheetVisible Application.DisplayAlerts = True Application.EnableEvents = True Cancel = True End If End If End Sub The down side of this solution is that one must list all other workbook sheets in the line following the "Else" statement. The "Cancel" command near the end of the code was necessary to prevent two (2) sheets from printing. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Workbook_BeforeClose(Cancel As Boolean) - Cancel won't work | Setting up and Configuration of Excel | |||
printing a worksheet | Excel Discussion (Misc queries) | |||
Printing worksheet | New Users to Excel | |||
Worksheet printing | Excel Worksheet Functions | |||
printing worksheet | Excel Discussion (Misc queries) |