Enable the Print Even after having Disabled i

Microsoft Excel 2003 (full product)
November 7, 2010 at 06:03:33
Specs: Windows Vista
Need help in achieving the following:

I have integrated the "BeforePrint" to disable the print event for the workbook but what I am looking for is some of the worksheets within the workbook needs to be printed and the remainder worksheets have to be disabled for printing. How can we manage that?

P.S.: I am using Excel 2003.

Thank you in advance!


See More: Enable the Print Even after having Disabled i

Report •

#1
November 7, 2010 at 06:20:59
Have the BeforePrint macro check the sheet names and only allow it to print the ones you want.

If you are unable to do that without our help, make sure you post your code so that we are not guessing about what you already have.

Post the code between the <pre> tags so that it maintains its format and makes it easier for us to follow.


Report •

#2
November 7, 2010 at 06:33:54
Thank you for your prompt reply, here below is the code I have inserted in the Workbook object. What I need to do is to enable the print only to those two mentioned worksheets that are part of the workbook.

Private Sub workbook_BeforePrint(Cancel As Boolean)
Select Case ActiveSheet.Name
	Case Sheetname = "Issues"
		Cancel = False
	Case Sheetname = "Actions"
		Cancel = False
	Case Else
		Cancel = True
		MsgBox ("Sorry but the Print Option has been disabled for this workbook"), vbInformation
End Select

End Sub


Report •

#3
November 7, 2010 at 07:33:17
Your use of the string "Sheetname" is the main problem. Your construction of the Select Case section is also incorrect.

"Sheetname" is not any type of VBA reserved word or object...it is nothing more than a variable name that you made up.

Since you don't set that variable equal to anything in your code, it is always empty and the Select Case always falls through to Case Else.

When using Select Case, the Case expressionlist always refers to the testexpression in the Select Case line. In other words, you don't need the "=" sign.

Try this:

Private Sub workbook_BeforePrint(Cancel As Boolean)
 Select Case ActiveSheet.Name
    Case "Issues"
        Cancel = False
    Case "Actions"
        Cancel = False
    Case Else
        Cancel = True
        MsgBox _
         ("Sorry but the Print Option has been disabled for this worksheet"), _
         vbInformation
 End Select

End Sub

or even this:

Private Sub workbook_BeforePrint(Cancel As Boolean)
 Select Case ActiveSheet.Name
    Case "Issues", "Actions"
        Cancel = False
    Case Else
        Cancel = True
        MsgBox _
         ("Sorry but the Print Option has been disabled for this worksheet"), _
         vbInformation
 End Select

End Sub


Report •

Related Solutions

#4
November 7, 2010 at 07:40:14
Thank you! works exactly the way I want.

Thank you so much and have a great weekend!


Report •

Ask Question