Code Fix Needed

[Phil Hageman]

The object of this code is to reset the scaling and print area in Page Setup – before printing begins. It is located in Module2 of the workbook. To test the code, I set the scale at 50% for the worksheets and clicked on the print button. It prints at 50% instead of 95%/90%, as proposed in the code. It’s as though Excel doesn’t see the code at all. Can someone look through this as suggest a fix? I can compress the file and send it if necessary.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wsSheet As Worksheet
Dim rng As Range, ar As Range
Dim lngZ As Long
For Each wsSheet In ActiveWindow.SelectedSheets
Select Case LCase(wsSheet.Name)
Case "Scorecard"
lngZ = 95
With wsSheet
Set rng = .Range("B1:BA45")
End With
Case "Customer", "Financial", "Learning and Growth", "Internal Business Process"
lngZ = 90
With wsSheet
Set rng = .Range("B1:BA32,B33:BA64,B65:BA96")
End With
Exit Sub
Case Else
With wsSheet.PageSetup
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
Exit Sub
End Select
With wsSheet.PageSetup
.Zoom = lngZ
End With
Cancel = True
On Error GoTo ErrHandler
Application.EnableEvents = False
For Each ar In rng
ar.PrintOut
Next
Next
ErrHandler:
Application.EnableEvents = True
End Sub

 

[Bob Phillips]

Phil,

In you Select statement you test the lowercase version of the sheet name,
but in the actual Case statements you use proper case. Cheng them to
lower-case (customer,scorecard), and all will be well.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

The object of this code is to reset the scaling and print area in Page

Setup - before printing begins. It is located in Module2 of the workbook.
To test the code, I set the scale at 50% for the worksheets and clicked on
the print button. It prints at 50% instead of 95%/90%, as proposed in the
code. It's as though Excel doesn't see the code at all. Can someone look
through this as suggest a fix? I can compress the file and send it if
necessary.

 

[Phil Hageman]

Bob, Thanks for answering my thread. I changed the capitalized letters to lower case and it still doesn't work. The names of the worksheets (on the tabs) is proper case, as typed in the Case sections, and I can't change that - this workbook is a template being used by a number of departements. I tried changing LCase to PCase (not a programmer, but thought what the heck) and that didn't work either. I see the logic of what you are saying but the fix aludes me. Any other ideas

Thanks,
Phil