Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ActiveCell's RangeName (XL97;SR2)

    Is it possible via VBA code to determine a range name of the activecell? If so, what would the code be.

    Thanks,
    John

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ActiveCell's RangeName (XL97;SR2)

    It's possible, but not too friendly. HTH --Sam
    <pre>Option Explicit
    '
    Sub findMyName()
    Dim N As Name
    For Each N In ActiveWorkbook.Names
    If N.RefersTo = "=" & ActiveSheet.Name & "!" & Selection.Address Then
    MsgBox N.Name & " is selected"
    Exit For
    End If
    Next N
    End Sub</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  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: ActiveCell's RangeName (XL97;SR2)

    Sammy,
    That works if the named range refers only to that cell - it won't give you a name if the activecell is part of a named range. (you could also use something like: msgbox selection.name.name syntax with an on error resume next to achieve the same thing). There is then the question of whether you want all ranges that overlap the entire selection, all ranges that overlap any of the cells in the selection, or only ranges that match the selection exactly....
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ActiveCell's RangeName (XL97;SR2)

    That's true Rory; guess I hadn't thought through the question. I was just so excited to finally use the Names collection. Also I noticed that I used Selection instead of ActiveCell. Does this give you enough information, John, to solve your problem or should I do a rewrite? --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

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

    Re: ActiveCell's RangeName (XL97;SR2)

    Can you be a little more specific about what you want? Are you looking for the cell's address, like A1, or are you looking for a defined name. If it is the defined name, do you want only a defined name that refers to just the activecell, or do you want the names of any or all defined names that include the active cell in their definition?
    Legare Coleman

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

    Re: ActiveCell's RangeName (XL97;SR2)

    The code below should find the first defined name that the active cell is contained in:

    <pre>Public Sub FindName()
    Dim strName As String
    Dim oName As Name
    For Each oName In ActiveWorkbook.Names
    If Not Intersect(oName.RefersToRange, ActiveCell) Is Nothing Then
    strName = oName.Name
    Exit For
    End If
    Next oName
    End Sub
    </pre>

    Legare Coleman

Posting Permissions

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