Page 1 of 3 123 LastLast
Results 1 to 15 of 39
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Name Manager (97/2000/2002)

    Hi everyone,

    Having to manage lots of defined names sometimes, I devised a little name manager.

    Anyone willing to Beta-test this ?
    Attached Files Attached Files
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  2. #2
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Name Manager (97/2000/2002)

    How did you know that's what I need right now? I'm taking it for a stroll......

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Name Manager (97/2000/2002)

    Jan Karel,
    I don't know if this is peculiar to my setup but I had to repoint the toolbar button to use:
    'Name Manager.xls'!Main.NameManager
    rather than
    'Name Manager.xls'!NameManager
    Also, the RefersTo box doesn't always seem to refresh itself - e.g. if you change from All Names to ActiveSheet Names, the names list clears but the RefersTo entry is still there.
    HTH.
    [Edit:]
    Sorry, should have mentioned I'm using Excel 2002!
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Name Manager (97/2000/2002)

    <hr>How did you know that's what I need right now<hr>

    Because I'm psychic! (<mutter> or is that psychedelic?) <g>
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Name Manager (97/2000/2002)

    On further investigation, the refersto box only fails to clear if there are no local sheetnames defined and you switch from all names to sheet names.
    HTH.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Name Manager (97/2000/2002)

    Added to my wishlist <bg>.

    Thanks.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Name Manager (97/2000/2002)

    I've also noticed that if you select Hidden Names, then close and reopen the Name Manager, the Hidden Names checkbox is cleared but hidden names still appear in the list. No big deal though.
    Since you mentioned wish lists, how about the ability to list all sheets with their locally defined names all at once? (I guess a TreeView would be good for that) <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Name Manager (97/2000/2002)

    Fixed the first few bugs, added the ability to hide/unhide.

    Dunno 'bout the sheets, maybe a sheet dropdown would be nice. Oh well, thanks for the comments so far.
    Attached Files Attached Files
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Name Manager (97/2000/2002)

    Nice. A couple of other thoughts (I'm not trying to be difficult - honest! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>)
    - it might be nice to be able to add names as well as change them.
    - could the RefersTo box be a RefEdit control so you can actually select the range in question?
    I like the idea of a listbox of sheetnames as an alternative to the treeview.
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Name Manager (97/2000/2002)

    I just tried the refedit control. It does enable me to select a range, but it is hard to manually edit the RefersTo Text because the cursor keys don't work in the control itself. Normally, one can toggle between cell selecting and editing by pressing F2, but that doesn't seem to work here. Since I have a lot of defined names that are in fact formulas iso normal references, I don't like this option very much.

    About the sheet level names.
    I could change the current toggle so that one either sees all names, or only sees all sheet-level names (regardless of what is the active sheet).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Brantford, Ontario, Canada
    Posts
    2,391
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Name Manager (97/2000/2002)

    Are you sure it's not psycho??? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Christopher Baldrey

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Name Manager (97/2000/2002)

    Jan Karel,
    Yes, I was playing around with the RefEdit control and discovered the same thing! One possible solution I tried was having a textbox in the same place as the refedit control. I then tested the RefersToRange property of the name, and if it errored, made the textbox visible instead with the refersto formula in it. There may be a nicer way!
    As for the sheets/global thing, I was trying 2 listboxes, one with sheetnames, the other with the relevant names in. Just a thought...
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Name Manager (97/2000/2002)

    One small thing with the revised version - if I choose Show Hidden Names and then select a hidden name, the Accept Changes and Cancel Changes buttons appear even though I haven't changed anything. I'm guessing it's due to the hidden checkbox checking itself.
    HTH.
    Regards,
    Rory

    Microsoft MVP - Excel

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Name Manager (97/2000/2002)

    No, if it were he would have been confined to the Access forum. The have all the straight jackets and padded cells over there.
    Legare Coleman

  15. #15
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Name Manager (97/2000/2002)

    <<padded cells >>

    And over here at Excel it's just cell mates.....
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Page 1 of 3 123 LastLast

Posting Permissions

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