Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'd like to test is a cell has a name and if so, find out what that name is. The name to reflect the cell only (so not refer to more cells).

    An example is probably a good thing... Some cells have a special purpose in my sheet; I am giving these (individual cells) a name, e.g. cell G3, when identified for the special purpose will have the name "Special_G3".

    Now if the user is doing something with a cell, I need to test in VBA is that cell is special, so I'd like to find out if that cell has a name and if that name is "Special_G3" in this case (also, and obviously, "Special_G3" should only apply to ONE cell (G3))

    I'm not sure how difficult this is, Range("G3").Name doesn't do the trick (that would have been too simple I guess )

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts
    If G3 has a name, you can use Range("G3").Name.Name to retrieve it.
    If G3 does not have a name, that would cause an error, so you'd have to use an error handler to suppress error messages.


  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    If G3 has a name, you can use Range("G3").Name.Name to retrieve it.
    If G3 does not have a name, that would cause an error, so you'd have to use an error handler to suppress error messages.
    OK, getting there... all that's left is giving the cell a name, I thought this would work (and sometimes it does, but most of the times I get an error):

    ActiveWorkbook.Names.Add Name:="Tag_" & ActiveCell.Address(False, False), RefersTo:=ActiveCell.Name

    In words: If the activecell is G10, I want to create a range name for cell G10 and name that "Tag_G10"

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts
    Change it to

    ActiveWorkbook.Names.Add Name:="Tag_" & ActiveCell.Address(False, False), RefersTo:=ActiveCell

  5. #5
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    Change it to

    ActiveWorkbook.Names.Add Name:="Tag_" & ActiveCell.Address(False, False), RefersTo:=ActiveCell
    Oops...

Posting Permissions

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