First, these kind of events don't belong in general modules (like Module2).

This one is a workbook event. Therefore, it should be located behind the
ThisWorkbook object.

I didn't test your code, but after you move it there (delete the version in
Module2), you still have a couple of typos.

For instance:
Select Case LCase(wsSheet.Name)
Case "Scorecard"

You say you want to look at the lower case name of the worksheet. But you
compare it to "Scorecard" with an uppercase S. It'll never match (well, unless
you have "option compare text" at the top of the module--but if you did, you
wouldn't need the LCase() stuff at all).



Phil Hageman wrote:

This code does not execute when the user clicks on the Print button. The code objective is stated below. One comment made is that in the select line, lower case is specified, where proper case is used in the case line. I changed the text in the case lines to lower case but the code still doesn't work. On thing: this workbook is a template used by many users. The worksheet tabs are typed in just as they appear in the code - Proper case. Does anyone have an idea to fix this code?

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. Its as though Excel doesnt 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

--

Dave Peterson