Get cell name into MsgBox?

[Ed from AZ]

I have about 200 cells with names inserted using Insert >> Name >>
Define. When I click on a cell, the name shows in the box on the
Formula bar. Often, though, the name is too long for the Name box.

I've been trying to find a way to connect the ActiveCell with the
Workbook Names collection to get the cell name into a message box, but
I haven't found an easy method. It almost looks like I would have to
build a string from the cell's sheet name and row and column
reference, then cycle through the workbook names until I found a name
with a matching RefersTo.

Is it really that complicated? Or is there an easier way?

Ed

 

[JW]

Here is a little function written by Chip Pearson. Should do the
trick for you.
Function NameOfParentRange(Rng As Range) As String
Dim Nm As Name
For Each Nm In ThisWorkbook.Names
If Rng.Parent.Name = Nm.RefersToRange.Parent.Name Then
If Not Application.Intersect(Rng, Nm.RefersToRange) _
Is Nothing Then
NameOfParentRange = Nm.Name
Exit Function
End If
End If
Next Nm
NameOfParentRange = ""
End Function

 

[JW]

Actually, give this a shot.
Sub foo()
On Error Resume Next
MsgBox ActiveCell.Name.Name
End Sub

 

[Jon Peltier]

Sub GetNameOfCell()
Dim nm As Name
For Each nm In ActiveWorkbook.Names
On Error Resume Next
If nm.RefersToRange.Address(External:=True) = _
ActiveCell.Address(External:=True) Then
If Err.Number = 0 Then
MsgBox nm.Name & vbNewLine & ActiveCell.Address
End If
End If
On Error GoTo 0
Next
End Sub

Or, widen the dropdown beneath the name box:

http://cpearson.com/excel/NameBox.htm

- Jon

 

[Bill Renaud]

If you simply want to see a list of all of the names in a workbook, what
I normally do is insert a new worksheet into the workbook, then use the
Insert|Name|Paste command to list them on the new worksheet.

1. Insert a new worksheet.
2. In cell A1, put "Name".
3. In cell B1, put "Refers to".
4. Format cells A1 and B1 as Bold, with cell underline (or however you
make a list).
5. Select cell A2, then choose the Name|Paste command from the Insert
menu. When the dialog box is displayed, click on the Paste List button.

You can then sort this list anyway you want for ease of use, or print
out the list.

CAUTION: Always use a blank worksheet for this command, since Excel does
NOT ask if it is about to overwrite any data!

 

[Ed from AZ]

I chose Chip's dropdown widening code. That works great!

Thanks to all for your assistance.

Ed