identify defined names in VBA code

**Mike1001** · 01-09-2008, 11:19 AM

Using Excel 2003 VBA, I couldn't find a possibility to find out the defined name of a selected range, especially when I don't know that range as in a Worksheet_Change macro, so I created the below function.

Does anybody know an easier way to do this without having to use the .ListNames command which will write the names and references into a spread sheet?

Any help will be welcome!

Mike

Public Function CellNickName(CellAddr, FirstListRow, FirstListColumn)
  ' CellAddr is the target.address (e.g. "$A$1") returned by a, e.g., Worksheet_Change macro
  Set li = Sheets("Lists")
  li.Cells(FirstListRow, FirstListColumn).ListNames ' Lists all Defined Names in the 1st specified column and references in the 2nd one
  tabName = "='" & ActiveSheet.Name & "'!" ' Identifies the sheet in which you are looking for defined names
  SecondListColumn = FirstListColumn + 1
  li.Columns(SecondListColumn).Replace What:=tabName, Replacement:="" ' removes "=Sheetname!" from cells
  CellNickName = ""
  i = 1
  Do Until IsEmpty(li.Cells(i, SecondListColumn)) ' check if the selected CellAddr matches a defined name
    If li.Cells(i, SecondListColumn) = CellAddr Then CellNickName = li.Cells(i, FirstListColumn): Exit Do
    i = i + 1
  Loop
  li.Columns(FirstListColumn).ClearContents: li.Columns(SecondListColumn).ClearContents
End Function

**SuitedAces** · 01-09-2008, 01:10 PM

Function ExactRangeName(Rng As Range) As String
On Error Resume Next
ExactRangeName = Rng.Name.Name
End Function

http://www.cpearson.com/excel/named.htm

example using target

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ExactRangeName(Target) <> "" Then
MsgBox ExactRangeName(Target)
End If
End Sub

condensed without using a seperate function

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
If Target.Name.Name = "InterestRate" Then
MsgBox "You changed the interest rate"
End If
ErrorHandler:
End Sub

**SuitedAces** · 01-10-2008, 03:37 PM

They teach good manners in germany apparently.

**Mike1001** · 01-23-2008, 11:41 AM

Thanks, SuitedAces, this is great help!

I wonder how someone finds out such syntax. I could not find Target.Name.Name in VBA Help.
Of course, I stumbled across Target.Name, but this only gives me "=Sheet1!$A$1".

You made my day!

Mike

identify defined names in VBA code

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

identify defined names in VBA code

You're my hero

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions