PDA

View Full Version : plz Help: allow certen sheet to be printed with condition



loveguy1977
03-09-2012, 04:20 AM
Dear Sirs,

There are 20 sheets say Sh1 to Sh20. How can I disable print if i'm in not allowed sheets.
Say I wanna print Sh1, Sh4, Sh9, Sh12 & Sh15 only
When any of these allowed sheets and if I press Print Botton, there is a condition as follow:

If I press Print when Sh1 or Sh9 or Sh12 or Sh15 is active then use this code



Sub PrintSheets_Condition1()
Dim aShtLst As Variant
aShtLst = Array("Sh1", "Sh9", "Sh12", "Sh15")
ThisWorkbook.Sheets(aShtLst).PrintOut
End Sub

If I press Print when Sh4 is active then use this code



Sub PrintSheets_Condition2()
Dim aShtLst As Variant
aShtLst = Array("Sh4", "Sh9", "Sh12", "Sh15")
ThisWorkbook.Sheets(aShtLst).PrintOut
End Sub

As well as in each of these sheets (i.e. [COLOR=#000000]Sh1, Sh4, Sh9, Sh12 & Sh15) there will be a shape to run these conditions
i.e. shape to run PrintSheets_Condition1 in Sh1, Sh9, Sh12, Sh15

shape to run PrintSheets_Condition2 in Sh4

Thank you

Bob Phillips
03-09-2012, 05:12 AM
Untested



Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim aShtLst As Variant
Select Case ActiveSheet.Name

Case "Sh1", "Sh9", "Sh12", "Sh15"

aShtLst = Array("Sh1", "Sh9", "Sh12", "Sh15")

Case "Sh4"

aShtLst = Array("Sh4", "Sh9", "Sh12", "Sh15")
End Select

ThisWorkbook.Sheets(aShtLst).PrintOut
End Sub

loveguy1977
03-09-2012, 06:12 AM
It is not working with me



Untested



Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim aShtLst As Variant
Select Case ActiveSheet.Name

Case "Sh1", "Sh9", "Sh12", "Sh15"

aShtLst = Array("Sh1", "Sh9", "Sh12", "Sh15")

Case "Sh4"

aShtLst = Array("Sh4", "Sh9", "Sh12", "Sh15")
End Select

ThisWorkbook.Sheets(aShtLst).PrintOut
End Sub

Bob Phillips
03-09-2012, 07:33 AM
That doesn't tell me much does it.