Disable Printing Of A Worksheet

  • HI there


    I have a workbook of some 15 worksheets - however there is just one of the worksheets I want to disable the printing of it. So that when users select print whole document - all worksheets will print except for thus one worksheet


    The worksheet tab is named "15.ADMIN WORKBOOK CONTROL ONLY"


    I know I need to put some vba code into the code area of that worksheet


    I tried this code but it didnt work

    Code
    Private Sub Worksheet_BeforePrint(Cancel As Boolean)
        Cancel = True
    End Sub


    any ideas please


    Thanks


    Tony Mos
    :)

  • Re: Disable Printing Of A Worksheet


    You could try this....



    HTH
    Ger

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

  • Re: Disable Printing Of A Worksheet


    Alternatively

    Code
    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Const sWs_Name As String = "15.ADMIN WORKBOOK CONTROL ONLY"
    
    
    If ActiveSheet.Name = sWs_Name Then Cancel = True: MsgBox sWs_Name & _
        " cannot be printed", vbCritical, "Print Alert"
    End Sub
  • Re: Disable Printing Of A Worksheet


    I tried this in the workbook module to only print Sheet3, and it seemed to work OK.


    You might need to set-up separate print buttons, however, if you want to allow them to both print up a single worksheet and the entire workbook.


    Using this approach, you could make MyTest a public variable, and then have the macro PrintMyStuff (and its "equivalents") in a standard module.

    Code
    Public MyTest as Boolean
  • Re: Disable Printing Of A Worksheet


    THANKS FOR ALL YOUR EFFORTS - BUT IT IS NOT DOING WHAT I NEED IT TO DO -PLEASE RE READ WHAT IS NEEDED AT THE BEGINING OF THIS THREAD.


    I HAVE A WORKBOOK OF ABOUT 15 worksheets - what is needed is if a user selects to print the Entire workbook - I want all worksheets in the workbook to print except for one worksheet.
    The name of the worksheet wanted to be printed with all the others is called
    "15. ADMIN WORKBOOK CONTROL ONLY"


    HOPE YOU CAN HELP - I AM AT A LOSS
    REGARDS


    TONY MOS

  • Re: Disable Printing Of A Worksheet


    Tony, with respect, I think my code does this.


    Ger

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

  • Re: Disable Printing Of A Worksheet


    Also, no need for capitalisation - this amounts to shouting in Forum etiquette!


    Also, I have just noticed that you didn't bother o use [url=http://www.ozgrid.com/forum/misc.php?do=bbcode#code] Code Tags] in your original post!


    I suggest you re-read the posts, you probably are not placing the code in the Workbook_BeforePrint event

  • Re: Disable Printing Of A Worksheet


    Maybe this is what you mean.


    The code only kicks in if the user selects ALL sheets in the workbook.
    It then unselects the sheet you do not want printed.


    [vba]Private Sub Workbook_BeforePrint(Cancel As Boolean)


    Dim strShtNames As String
    Dim lngIndex As Long
    Dim shtTemp As Object
    Dim strJoin As String

    With ActiveWindow
    If .SelectedSheets.Count = .Parent.Sheets.Count Then
    ' All sheets selected - does not take into account hidden sheets
    For Each shtTemp In .SelectedSheets
    ' change name of sheet to exclude
    If shtTemp.Name <> "Sheet2" Then
    strShtNames = strShtNames & strJoin & shtTemp.Name
    strJoin = ","
    End If
    Next
    ActiveWindow.Parent.Sheets(Split(strShtNames, ",")).Select
    End If
    End With

    End Sub[/vba]

  • Re: Disable Printing Of A Worksheet


    Again thanks everyone for your efforts -much appreciated


    I am obviosuly doing something wrong
    -so I have attached a TEST FILE


    I have used ANDY POPE's code in this one - then selected print and selected entire workbook but still all of the worksheets still printed out.


    What I want to acheive here is if I print and select entire workbook only sheet 1 and 2 with print and sheet3 is not to be printed out.


    Could have a look at the test file you let me know what it is that I am doing wrong - why it not working - Its driving me nuts


    Thanks heaps


    Tony Mos

  • Re: Disable Printing Of A Worksheet


    This should do what you want:


    it will not print sheet3 when you goto print entire workbook.



    or see the attached file

  • Re: Disable Printing Of A Worksheet


    Hi everyone - you all have been great and very helpful


    The last code suggestion worked great for the default 3 worksheet new excel file I attached previously - however the code doesn't work when I add it to the real file I am working on. I have changed the tab name from sheet3 to the name of the tab of the real file I am working on but it wont work still.


    Therefore as a last resort I have attached a zipped file of a the excel file I am working on minus the hard data. Again the aim is when a user selects print ENTIRE workbook it all prints except for one worksheet in the case of the real file attached that worksheet tab name is ADMIN.


    Could you please let me know what it is that is stopping the code in the workbook object from working?


    Who ever can tell me what is that I am missing will make it onto my email Christmas card list. Hope you can help


    Regards



    Tony Mos

  • Re: Disable Printing Of A Worksheet


    Hi – again thank you so much for your efforts in trying to help me


    The last code that was sent works great in the test file I posted. However when I place the code in the real file I am working on and change to worksheet name to the name of the tab in the real workbook – named ADMIN the code didn’t work. I have tried several modifications to the code but to no avail – there is obvious something I am missing so the only way to is if any one can spot my mistake is to post a zipped copy of the real file I am working on minus the hard data.


    Again my aim is that if a users selects print the ENTIRE workbook that all worksheets will print except for one specific worksheet –which in the case of the real file attached the real worksheet tab name is ADMIN.


    Who ever can tell me what is that I am missing will make it onto my email Christmas card list. Hope you can help


    Regards



    Tony Mos

  • Re: Disable Printing Of A Worksheet


    Hi Tony,


    I just downloaded the attachment you provided. I unzipped and clicked print.


    The Admin page did not print.


    What happens if you click print and then click preview does the page appear in the preview?

  • Re: Disable Printing Of A Worksheet


    Also try scrolling accross the sheet tabs until the admin page is in view then click print. Does the admin tab dissapear. If no there is something wrong.


    Make sure you have macros enabled?

  • Re: Disable Printing Of A Worksheet - thankyou


    It worked !! and yes the ADMIN tab disappeared when I pressed print


    Thanks for your help


    And my appreciation and a BIG thankyou to those people who have helped


    Regards


    Tony Mos

  • Re: Disable Printing Of A Worksheet


    Guys, i still cant make it work. I tryed to to print your TEST FILE and it actually doesnt work either. It doesnt want to print entire book, only the active sheet .... what is wrong? Could it be because of Excel 2010?

  • Re: Disable Printing Of A Worksheet


    Hi Yika - welcome to the forum.


    This is a very old thread and has been solved. You should not resurrect it. Please start a new thread if you have a question. You can reference to this thread by URL link if you believe it will help.


    Thread closed.


    Thanks,
    Ger

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!