How do I display cell name with Msgbox?

[nomail1983]

As I said before, I left my VBA programming book at home. So I'm
flying blind. Sorry for the rudimentary questions.

How do I display the cell name with Msgbox?

For example (pseudocode):

function myfunc(arg)
if TypeName(arg) = "Range" then
for each cell in arg
Msgbox cell.Name
next
end if
end function

 

[I.R;))]

As I said before, I left my VBA programming book at home. So I'm
flying blind. Sorry for the rudimentary questions.

How do I display the cell name with Msgbox?

For example (pseudocode):

function myfunc(arg)
if TypeName(arg) = "Range" then
for each cell in arg
Msgbox cell.Name
next
end if
end function

In VBA editor you must DBLClick on sheet (no module), for example
"Sheet1" and paste this source

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox (Target.Address)
End Sub

 

[Mike H]

Hi,

Sub ListNamedRanges()
Dim objName As Name
For Each objName In ThisWorkbook.Names
MsgBox objName.Name & objName.RefersTo
Next
End Sub

Mike

 

[nomail1983]

How do I display the cell name with Msgbox?

[....]
MsgBox (Target.Address)

Perfect! Thanks.

 

[I.R;))]

How do I display the cell name with Msgbox?

[....]
MsgBox (Target.Address)

Perfect! Thanks.

No problemo!!!!

 

[Bill Renaud]

Looks like it's about time to start learn to use the Object Browser, and
the Locals and Immediate windows in the VBA editor. (Most of my VBA
books stay on the shelf anymore also.)

Since you know that you are probably looking for a property or something
that is related to a Range, open up the Object Browser (<F2> key), then
type Range into the "Search Text" combo box. You could also choose
"Excel" in the "Project/Library" combo box right above it to limit the
search. Search Results will show a table with the first column being
"Library", the next being Class, and the last being Member. Click on the
row that has Excel in the Library column and Range in the Class column.
2 list boxes below the "Search Results" will show Classes and Members of
'Range'. Select "Range" in the Classes list box, then scroll up and down
the list of Members in the list box to the right. Notice that "Address"
is one of the Members. Click on it and see the arguments, return type,
and read/write status in the gray area at the bottom.

Alternatively, type in a quick demo routine, like below:

Public Sub Test()
Dim rng As Range

Set rng = ActiveCell
End Sub

Single-step through the routine, until reaching the End Sub line. In the
locals window, open up the "rng" object and see if you can find any
properties that look like they might give you what you want.

Also, open up the Immediate window, and type in the following (after you
type the period, auto-data members should bring up a drop down box so
the "Address" can be selected without having to type it all (be sure to
include the "?" at the front end):

?rng.Address

After pressing Enter, the VBA run-time will return the current result:

$A$1

Maybe this is what you are looking for? Try different properties (edit
the original line) until you see something interesting, then use Help to
get the usage details.

Sorry if this reply is sounds condescending, but we all (including
me!!!) need to get better at looking up stuff using all of the online
resources! I realize Microsoft didn't explain the VBA environment very
well or how to use the editor features to the fullest extent. It took me
quite a while to learn them also!!!