Results 1 to 3 of 3

ActiveSheet.Names.Add

This is a discussion on ActiveSheet.Names.Add within the Excel Questions forums, part of the Question Forums category; I'm using the following code to provide initial print settings when the user opens this workbook. Code: Private Sub Workbook_Open() ...

  1. #1
    Board Regular hatman's Avatar
    Join Date
    Apr 2005
    Location
    Palmer, MA
    Posts
    2,606

    Default ActiveSheet.Names.Add

    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.

  2. #2
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    3,673

    Default

    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.
    Jon Peltier
    Peltier Technical Services, Inc.
    Try the Peltier Tech Chart Utility

  3. #3
    Board Regular hatman's Avatar
    Join Date
    Apr 2005
    Location
    Palmer, MA
    Posts
    2,606

    Default

    Quote Originally Posted by Jon Peltier
    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.

    Quote Originally Posted by Jon Peltier
    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.

    Quote Originally Posted by Jon Peltier
    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.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com