Code modification to accomodate scaling and print areas.

[Guest]

The following code is intended to get around users changing the scaling setting in page setup – and thereby messing up the print. I need to modify it to accommodate two issues – scale size, and print areas
Worksheet “Scorecard†scale is 95%, one print range: B1:BA4
Worksheet “Customer†scale is 90%, three print ranges: B1:BA32, B33:BA64, B65:BA9
Worksheet “Financial†scale is 90%, three print ranges: B1:BA32, B33:BA64, B65:BA9
Worksheet “Learning†scale is 90%, three print ranges: B1:BA32, B33:BA64, B65:BA9
Worksheet “Process†scale is 90%, three print ranges: B1:BA32, B33:BA64, B65:BA9

(If the print range is not given, Excel will print all three ranges on one sheet.

Private Sub Workbook_BeforePrint(Cancel As Boolean
Dim wsSheet As Workshee
For Each wsSheet In ActiveWindow.SelectedSheet
With wsSheet.PageSetu
.Zoom = Fals
.FitToPagesWide =
.FitToPagesTall =
End Wit
End Su

 

[Tom Ogilvy]

Assume if you have multiple sheets, they will include only those you have
identified. Any other sheet will be printed singlely. Assume you want to
print each one of your ranges on a separate sheet.

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 lcase(wsSheet.Name)
Case "scorecard"
lngZ = 95
With wsSheet
set rng = .Range("B1:BA45")
End With
Case "Customer", "Financial", "Learning", "Process"
lngZ = 90
With wsSheet
set rng = .Range("B1:BA32,B33:BA64,B65:BA96")
Exit Sub
Case Else
With wsSheet.PageSetup
.Zoom =
.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
ErrHandler:
Application.EnableEvents = True
End Sub


--
Regards,
Tom Ogilvy

The following code is intended to get around users changing the scaling

setting in page setup - and thereby messing up the print. I need to modify
it to accommodate two issues - scale size, and print areas:

 

[Guest]

Tom,

Thanks for your reply. Your assumptions about the worksheets are all correct

I pasted the code into a general module, and am receiving a compile error on the sixth line - the text Icase is highlighted, with a message "Expected: Case"

As a test, using Page Setup, I resized all the worksheets to 50%, expecting the code to make printed copy defined and sized per the code. It prints as 50%. Maybe correction of the compile error will make the code work

Phil

 

[Tom Ogilvy]

there is no Icase in the code

there is

? lcase(wsSheet.name)
sheet2

Which works fine.

However, in that lin of code, CASE got omitted. It should be

Select Case LCase(wsSheet.Name)

But there were a couple of other typos. Try this

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", "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


--
Regards,
Tom Ogilvy

Tom,

Thanks for your reply. Your assumptions about the worksheets are all correct.

I pasted the code into a general module, and am receiving a compile error

on the sixth line - the text Icase is highlighted, with a message "Expected:
Case".

As a test, using Page Setup, I resized all the worksheets to 50%,

expecting the code to make printed copy defined and sized per the code. It
prints as 50%. Maybe correction of the compile error will make the code
work.

 

[Guest]

Tom

I entered the code and it doesn't work. Likely, I have omitted an important point in problem description. Can I send this file to you

Phil