hatman
Well-known Member
- Joined
- Apr 8, 2005
- Messages
- 2,664
I'm using the following code to provide initial print settings when the user opens this workbook.
Everything works as expected EXCEPT for assigning the formula to Print_Area. Rather than inserting the expected formula, instead it inserts the resulting range when the formula is evaluated. In other words, when I go into insert->names->define and view the Print_Area name, I see a hard range rather than the expected formula. And of course, the whole point of the formula is to dynamically update the Print_Area based calculations that are done elsewhere.
The kicker is that when I copy this line and paste it into a Sub in another module, and then execute it, it works as expected. But when I call that Sub from the Open event, it gives erroneous results.
Any insights out there? I am assuming that there is some sort of limitation based on the class module... but I have no idea how to sort it out.
Code:
Private Sub Workbook_Open()
pgbrk = Array("P1", "W1", "AD1", "AK1", "AR1", "AY1")
ActiveWorkbook.Names.Add Name:="Print_Area", RefersTo:="=OFFSET(Input_Output!$A$1,0,0,pr_row,pr_col)"
ActiveSheet.PageSetup.Orientation = xlLandscape
ActiveSheet.PageSetup.Zoom = 100
cnt = 1
For Each Item In pgbrk
Set ActiveSheet.VPageBreaks(cnt).Location = Range(Item)
cnt = cnt + 1
Next Item
End Sub
Everything works as expected EXCEPT for assigning the formula to Print_Area. Rather than inserting the expected formula, instead it inserts the resulting range when the formula is evaluated. In other words, when I go into insert->names->define and view the Print_Area name, I see a hard range rather than the expected formula. And of course, the whole point of the formula is to dynamically update the Print_Area based calculations that are done elsewhere.
The kicker is that when I copy this line and paste it into a Sub in another module, and then execute it, it works as expected. But when I call that Sub from the Open event, it gives erroneous results.
Any insights out there? I am assuming that there is some sort of limitation based on the class module... but I have no idea how to sort it out.