Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printing
Hi, Happy New Year to All
I use the code below that is placed in the ThisWorkbook to stop the printing of certain sheets; 'Stops sheets printing as listed in Case Private Sub Workbook_BeforePrint(Cancel As Boolean) Select Case ActiveSheet.Name Case "Input", "sheet1", "sheet2", "sheet3", "sheet4" Cancel = True MsgBox "Sorry, you cannot print this page", vbInformation End Select End Sub Is it possible to amend this to add an If statement to do the following; Look at cell E162 on sheet 1, if the value is True, sheets 3 and 4 now print, but sheets 1 & 2 stay so they don't print? Hope that makes sense. Regards Newbeetle |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printing
Hi,
You can do it all in the select statement checking for the true for the two sheets. I have introduced a variable cannotprint which is set to true if you cannot print. Private Sub Workbook_BeforePrint(Cancel As Boolean) dim connotprint as boolean Select Case ActiveSheet.Name Case "Input", "sheet1", "sheet2" cannotprint=true case "sheet3", "sheet4" cannotprint = worksheets("sheet1").range("E162") case else cannotprint=true end select if cannotprint then Cancel = connotprint MsgBox "Sorry, you cannot print this page", vbInformation end if End Sub Is it possible to amend this to add an If statement to do the following; Look at cell E162 on sheet 1, if the value is True, sheets 3 and 4 now print, but sheets 1 & 2 stay so they don't print? -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Newbeetle" wrote: Hi, Happy New Year to All I use the code below that is placed in the ThisWorkbook to stop the printing of certain sheets; 'Stops sheets printing as listed in Case Private Sub Workbook_BeforePrint(Cancel As Boolean) Select Case ActiveSheet.Name Case "Input", "sheet1", "sheet2", "sheet3", "sheet4" Cancel = True MsgBox "Sorry, you cannot print this page", vbInformation End Select End Sub Is it possible to amend this to add an If statement to do the following; Look at cell E162 on sheet 1, if the value is True, sheets 3 and 4 now print, but sheets 1 & 2 stay so they don't print? Hope that makes sense. Regards Newbeetle |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printing
Just a warning...
The user can select multiple sheets and if one of those sheets isn't the activesheet, that user can print what he/she wants. Newbeetle wrote: Hi, Happy New Year to All I use the code below that is placed in the ThisWorkbook to stop the printing of certain sheets; 'Stops sheets printing as listed in Case Private Sub Workbook_BeforePrint(Cancel As Boolean) Select Case ActiveSheet.Name Case "Input", "sheet1", "sheet2", "sheet3", "sheet4" Cancel = True MsgBox "Sorry, you cannot print this page", vbInformation End Select End Sub Is it possible to amend this to add an If statement to do the following; Look at cell E162 on sheet 1, if the value is True, sheets 3 and 4 now print, but sheets 1 & 2 stay so they don't print? Hope that makes sense. Regards Newbeetle -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printing
Hi Martin, I tried out the code but I'm having a few problems so I've done something wrong. In case it was other item's in my workbook interfering I made a new workbook and placed the following code in the VBA Thisworkbook Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim connotprint As Boolean Select Case ActiveSheet.Name Case "sheet1", "sheet2" cannotprint = True Case "sheet3", "sheet4" cannotprint = Worksheets("sheet1").Range("E1") Case Else cannotprint = True End Select If cannotprint Then Cancel = connotprint MsgBox "Sorry, you cannot print this page", vbInformation End If End Sub I then put test text on the four sheets, when pressing the print button, the message box appears for each sheet, but when I click the ok button the page still prints. I've tried a few things but really I'm a bit lost. "Martin Fishlock" wrote: Hi, You can do it all in the select statement checking for the true for the two sheets. I have introduced a variable cannotprint which is set to true if you cannot print. Private Sub Workbook_BeforePrint(Cancel As Boolean) dim connotprint as boolean Select Case ActiveSheet.Name Case "Input", "sheet1", "sheet2" cannotprint=true case "sheet3", "sheet4" cannotprint = worksheets("sheet1").range("E162") case else cannotprint=true end select if cannotprint then Cancel = connotprint MsgBox "Sorry, you cannot print this page", vbInformation end if End Sub Is it possible to amend this to add an If statement to do the following; Look at cell E162 on sheet 1, if the value is True, sheets 3 and 4 now print, but sheets 1 & 2 stay so they don't print? -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Newbeetle" wrote: Hi, Happy New Year to All I use the code below that is placed in the ThisWorkbook to stop the printing of certain sheets; 'Stops sheets printing as listed in Case Private Sub Workbook_BeforePrint(Cancel As Boolean) Select Case ActiveSheet.Name Case "Input", "sheet1", "sheet2", "sheet3", "sheet4" Cancel = True MsgBox "Sorry, you cannot print this page", vbInformation End Select End Sub Is it possible to amend this to add an If statement to do the following; Look at cell E162 on sheet 1, if the value is True, sheets 3 and 4 now print, but sheets 1 & 2 stay so they don't print? Hope that makes sense. Regards Newbeetle |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 printing problem--printing 1 document on 2 pages | Excel Discussion (Misc queries) | |||
Excel Printing --Borders are not printing on the same page as data | Excel Discussion (Misc queries) | |||
Printing a heading on each new page when printing | Excel Discussion (Misc queries) | |||
Enable Double sided printing contiuously when printing multiple s. | Excel Discussion (Misc queries) | |||
Printing? Worksheets not printing the same on multiple pc's! | Excel Programming |