ActiveSheet.Names.Add

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.

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.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
First, the defined name Print_Area is worksheet level, so you need to reference the sheet:

ActiveWorkbook.Names.Add Name:="'" & activesheet.name & "'!Print_Area"

Second, Print_Area is a reserved name. I would have thought Excel would just choke on it, but when I actually tested it (manually, not thru code), it accepted it, but the hourglass stayed for 20 or 30 seconds whenever I edited the name's refers to formula.

It's probably better to use worksheet events (open, activate, calculate, change, whatever) to change the print area, without relying on a dynamic formula for its definition.
 
Upvote 0
Jon Peltier said:
First, the defined name Print_Area is worksheet level, so you need to reference the sheet:

ActiveWorkbook.Names.Add Name:="'" & activesheet.name & "'!Print_Area".

Good point, I forgot about that.

Jon Peltier said:
Second, Print_Area is a reserved name. I would have thought Excel would just choke on it, but when I actually tested it (manually, not thru code), it accepted it, but the hourglass stayed for 20 or 30 seconds whenever I edited the name's refers to formula.

Yeah, I stumbled across this one myself... and I was equally suprised when it worked. I can't say that I have found the calculation to take any longer with this usage in the one workbook where i am using it. But I will look a littl ebit more closely at system resource management before I turn this loose... it is something I really only stumbled on in the last few weeks.

Jon Peltier said:
It's probably better to use worksheet events (open, activate, calculate, change, whatever) to change the print area, without relying on a dynamic formula for its definition.

The one benefit to using the formula is that it dynamically updates on re-display or re-calculate, but if the user goes into the Page Break Preview and drags the blue lines around, because it is a system variable, Excel replaces the formula with the new range defined by the user. This gives me an elegant way of creating a default print setup at startup that will work, but still allows the user to customize freely if he wants to print only an excerpt, for example.

Thanx for the feedback, you have given me a few more ideas, but if you happen to think of any more, please share.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top