Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Lounger
    Join Date
    Jul 2003
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Prevent printing of single worksheet (Excel XP)

    Hi,

    I have an excel workbook with several sheets. I want to prevent the possibility printing of one of the sheets. Does anyone know if I can block this?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Prevent printing of single worksheet (Excel XP)

    You could add something like this to the before print event of the workbook to prevent the sheet named "Sheet1" from printing (change as appropriate). it does NOT prevent other ways to get around around this. (renaming the sheet, copying the sheet, print-screen, have cell references to the sheet, etc) only literally printing the sheet.

    How "protected" does it need to be and what all needs to be prevented?

    Steve

    <pre>Option Explicit
    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim sDoNotPrint As String
    Dim sht
    sDoNotPrint = "Sheet1"
    For Each sht In ActiveWorkbook.Windows(1).SelectedSheets
    If sht.Name = sDoNotPrint Then
    MsgBox ("You can NOT print '" & sDoNotPrint & "'" _
    & vbCrLf & "Printing cancelled")
    Cancel = True
    End If
    Next sht
    End Sub</pre>


  3. #3
    Lounger
    Join Date
    Jul 2003
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prevent printing of single worksheet (Excel XP)

    'it's just a security for ourself, so that we not accidentaly print out this sheet and send it to a customer. Can you tell me where i have to put this code (i'm a big Excel freak)

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Prevent printing of single worksheet (Excel XP)

    goto vb(atl-f11)
    project explorer (ctrl-r)
    In the tree view find the workbook you want this in
    dbl-click on thisworkbook "object"
    copy the code I provided
    In the macro pane (usually on the upper right )
    Paste the code
    Change the name of the sheet in the line:
    <pre>sDoNotPrint = "Sheet1"</pre>

    quit vb (ctrl-Q)

    Steve

  5. #5
    Lounger
    Join Date
    Jul 2003
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prevent printing of single worksheet (Excel XP)

    unfortunately it's not working. My sheet is named "BEREKENING" so i put in the following code:

    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim sDoNotPrint As String
    Dim sht
    sDoNotPrint = "BEREKENING"
    For Each sht In ActiveWorkbook.Windows(1).SelectedSheets
    If sht.Name = sDoNotPrint Then
    MsgBox ("Je mag niet printen '" & sDoNotPrint & "'" _
    & vbCrLf & "Printing cancelled")
    Cancel = True
    End If
    Next sht
    End Sub

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Prevent printing of single worksheet (Excel XP)

    Could you give more details? What is not working?
    Do you get an error?
    does it just print normal?

    When I tested it:
    If I ONLY sheet1 selected and try to print I get the msgbox and print is cancelled
    If I have MULTIPLE sheets selected and try to print I get the msgbox and print is cancelled

    How are you printing? Have you enabled macros?

    I need more troubleshooting info.

    I did attach my working test spreadsheet for you to examine.

    Steve

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Prevent printing of single worksheet (Excel XP)

    The comparison is case-sensitive, so if your sheet is named "Berekening", it is not equal to sDoNotPrint = "BEREKENING". You can provide for this by testing

    If UCase(sht.Name) = UCase(sDoNotPrint) Then

  8. #8
    Lounger
    Join Date
    Jul 2003
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prevent printing of single worksheet (Excel XP)

    I've opened your XLS sheet, but i can't find the coding. I think i have put it on the wrong location. I indeed havel muliple sheets, and my sheet name is BEREKENING in capitals...

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Prevent printing of single worksheet (Excel XP)

    As i mentioned earlier it is in the Thisworkbook object (not a MODULE)

    goto vb(atl-f11)
    project explorer (ctrl-r)
    In the tree view find my test workbook
    dbl-click on the object literally name "ThisWorkbook"

    The code is in there.

    Steve

  10. #10
    Lounger
    Join Date
    Jul 2003
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prevent printing of single worksheet (Excel XP)

    OK, this works. Now when I individually want to print this sheet, i get the message but,
    when i select print / Whole workbook the sheet is being printed.

    Thanks again in advance

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Prevent printing of single worksheet (Excel XP)

    That is one of the many workarounds to the code I provided.
    The dialog box for printing comes AFTER the before print event. If that sheet is NOT selected then the printing won't be stopped.

    It is NOT clear from your note, do you need to prevent this event also? Do you need to be able to print it if desired?
    Steve

  12. #12
    Lounger
    Join Date
    Jul 2003
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prevent printing of single worksheet (Excel XP)

    ok, this is my final desire: also when i choose to print AND the sheet is not selected, it also has to be prevented from printing

  13. #13
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Prevent printing of single worksheet (Excel XP)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> gerbenhk

    How about writing some VBA code that would handle the printing of your workbook?

    It will print ALL worksheets minus the one you don't want to print? You can modify the code presented in some messages in this thread, by adding a test (If statement) to check if you are working with the protected worksheet and not print it, and skip to the next one.

    Or

    You can place the worksheet as the first or the last worksheet in the workbook and use a For-Next loop starting with 2 for the second sheet, or ending with Thisworkbook.worksheets.Count -1 for the last worksheet exclusion.

    Or

    you can assign it the VeryHidden status and this means its well protected, even from many human beings.

    Or

    You can have this worksheet in a different workbook altogether.

    Hope this helps.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Prevent printing of single worksheet (Excel XP)

    Try this.
    If "BEREKENING" is selected either alone or with a group printing is cancelled.

    If multiple sheets are selected and they DO NOT contain BEREKENING then the selected sheets will be printed

    Otherwise you will be asked if you want to print all, if yes all (but BEREKENING) will be printed. If no only the selected sheet.

    Steve


    <pre>Option Explicit
    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim sDoNotPrint As String
    Dim sht
    Dim PrintAll As Integer

    On Error GoTo ErrHandler
    sDoNotPrint = "Sheet1"
    Cancel = True
    For Each sht In ActiveWorkbook.Windows(1).SelectedSheets
    If sht.Name = sDoNotPrint Then
    MsgBox ("You can NOT print '" & sDoNotPrint & "'" _
    & vbCrLf & "Printing cancelled")
    Exit Sub
    End If
    Next sht

    Application.EnableEvents = False
    If ActiveWorkbook.Windows(1).SelectedSheets.Count = 1 Then
    PrintAll = MsgBox(prompt:="Do you want to Print all the sheets?", _
    Buttons:=vbYesNo)

    If PrintAll = vbYes Then
    For Each sht In ActiveWorkbook.Sheets
    If sht.Name <> sDoNotPrint Then _
    sht.PrintOut
    Next sht
    Else
    ActiveSheet.PrintOut
    End If
    Else
    ActiveWorkbook.Windows(1).SelectedSheets.PrintOut
    End If

    ExitHandler:
    Application.EnableEvents = True
    Exit Sub

    ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
    End Sub</pre>


  15. #15
    Lounger
    Join Date
    Jul 2003
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prevent printing of single worksheet (Excel XP)

    Steve,

    With this coding I don't get the print options ( to select the type of printer etc). Is there a possibility to let the code run, after the print dialog?

    Gerben

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •