Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Fix Needed
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Fix Needed
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) "Phil Hageman" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Fix Needed
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA code needed | New Users to Excel | |||
Code Needed | New Users to Excel | |||
VBA code help needed | Excel Discussion (Misc queries) | |||
VBA code Help needed | Excel Programming | |||
Fw:code needed | Excel Programming |