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