Is there a fast simple way of determining which shape is left click selected
to a macro? I want the same speed and methodology as when a cell is left
click selected resulting with a Target range being provided to a macro. The
situation is at least 200+ lines (Arrows) on a spreadsheet..

Hi Ray,
If a shape is selected then Shape.Name returns its name.
If the selection is a named range then Shape.Name returns the RefersTo
eg "=Sheet1!$A$1"
If the selection is an unnamed range then an error occurs.
So if your sheet has no named ranges you could use something like...

Public Sub WhichShape()
Dim strSelectedShape As String
On Error GoTo NONAME
strSelectedShape = Selection.Name
MsgBox strSelectedShape
NONAME:
End Sub

If there is a named range(s) on the sheet then you might have to test
for the presence of the "=" in the string strSelectedShape...

Public Sub WhichShape()
Dim strSelectedShape As String
On Error GoTo NONAME
strSelectedShape = Selection.Name
If Left(strSelectedShape, 1) < "=" Then
MsgBox strSelectedShape
End If
NONAME:
End Sub

Ken Johnson

That's not what I had in mind. I put your code into the VBA code associated
with a blank spreadsheet and added the Worksheet_SelectionChange() code on
the same sheet as follows:

Public Sub WhichShape()
Dim strSelectedShape As String
On Error GoTo NONAME
strSelectedShape = Selection.Name
MsgBox strSelectedShape
NONAME:
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox "targetaddress is " & Target.AddressLocal
End Sub

Then I placed some arrows on the sheet, to provide a shape to select.

When I select a cell, I get the message box stating its address. When I
select a line, nothing happens. Why does one work, and not the other?



"Ken Johnson" wrote in message
oups.com...
Hi Ray,
If a shape is selected then Shape.Name returns its name.
If the selection is a named range then Shape.Name returns the RefersTo
eg "=Sheet1!$A$1"
If the selection is an unnamed range then an error occurs.
So if your sheet has no named ranges you could use something like...

Public Sub WhichShape()
Dim strSelectedShape As String
On Error GoTo NONAME
strSelectedShape = Selection.Name
MsgBox strSelectedShape
NONAME:
End Sub

If there is a named range(s) on the sheet then you might have to test
for the presence of the "=" in the string strSelectedShape...

Public Sub WhichShape()
Dim strSelectedShape As String
On Error GoTo NONAME
strSelectedShape = Selection.Name
If Left(strSelectedShape, 1) < "=" Then
MsgBox strSelectedShape
End If
NONAME:
End Sub

Ken Johnson

SelectionChange is only triggered by changing the cell selection. Selecting
an object triggers no worksheet event.

--
Regards,
Tom Ogilvy

"Ray Pixley" wrote in message
news:C8%Zf.1683$8g3.680@trnddc02...
That's not what I had in mind. I put your code into the VBA code
associated
with a blank spreadsheet and added the Worksheet_SelectionChange() code on
the same sheet as follows:

Public Sub WhichShape()
Dim strSelectedShape As String
On Error GoTo NONAME
strSelectedShape = Selection.Name
MsgBox strSelectedShape
NONAME:
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox "targetaddress is " & Target.AddressLocal
End Sub

Then I placed some arrows on the sheet, to provide a shape to select.

When I select a cell, I get the message box stating its address. When I
select a line, nothing happens. Why does one work, and not the other?



"Ken Johnson" wrote in message
oups.com...
Hi Ray,
If a shape is selected then Shape.Name returns its name.
If the selection is a named range then Shape.Name returns the RefersTo
eg "=Sheet1!$A$1"
If the selection is an unnamed range then an error occurs.
So if your sheet has no named ranges you could use something like...

Public Sub WhichShape()
Dim strSelectedShape As String
On Error GoTo NONAME
strSelectedShape = Selection.Name
MsgBox strSelectedShape
NONAME:
End Sub

If there is a named range(s) on the sheet then you might have to test
for the presence of the "=" in the string strSelectedShape...

Public Sub WhichShape()
Dim strSelectedShape As String
On Error GoTo NONAME
strSelectedShape = Selection.Name
If Left(strSelectedShape, 1) < "=" Then
MsgBox strSelectedShape
End If
NONAME:
End Sub

Ken Johnson

Your original post wasn't visible when I answered your followup. My answer
was given from the perspective of selection a shape.

For clicking on a shape:
You can assign a single macro to each shape.

write a macro

Sub Line_Click()
Dim s as String, ln as Line
s = Application.Caller
set ln = activesheet.Lines(s)
msgbox ln.Name & " over cell " & ln.TopLeftCell.Address
End Sub


--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
SelectionChange is only triggered by changing the cell selection.
Selecting
an object triggers no worksheet event.

--
Regards,
Tom Ogilvy

"Ray Pixley" wrote in message
news:C8%Zf.1683$8g3.680@trnddc02...
That's not what I had in mind. I put your code into the VBA code
associated
with a blank spreadsheet and added the Worksheet_SelectionChange() code
on
the same sheet as follows:

Public Sub WhichShape()
Dim strSelectedShape As String
On Error GoTo NONAME
strSelectedShape = Selection.Name
MsgBox strSelectedShape
NONAME:
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox "targetaddress is " & Target.AddressLocal
End Sub

Then I placed some arrows on the sheet, to provide a shape to select.

When I select a cell, I get the message box stating its address. When I
select a line, nothing happens. Why does one work, and not the other?



"Ken Johnson" wrote in message
oups.com...
Hi Ray,
If a shape is selected then Shape.Name returns its name.
If the selection is a named range then Shape.Name returns the RefersTo
eg "=Sheet1!$A$1"
If the selection is an unnamed range then an error occurs.
So if your sheet has no named ranges you could use something like...

Public Sub WhichShape()
Dim strSelectedShape As String
On Error GoTo NONAME
strSelectedShape = Selection.Name
MsgBox strSelectedShape
NONAME:
End Sub

If there is a named range(s) on the sheet then you might have to test
for the presence of the "=" in the string strSelectedShape...

Public Sub WhichShape()
Dim strSelectedShape As String
On Error GoTo NONAME
strSelectedShape = Selection.Name
If Left(strSelectedShape, 1) < "=" Then
MsgBox strSelectedShape
End If
NONAME:
End Sub

Ken Johnson

So if I have 500 lines, I have to write 500 macros? I could, but that
sounds like overkill, and probably won't work. While I thought this info was
unnecessary to answer the question, you are probably assuming the line name
has the string "Line" in them and are sequentially numbered. They don't and
they won't be. Their names will not be predictable when writing the macro.
Consequently, to do as you suggest, I need to have a macro write a macro or
some way of "late-binding" a macro to a shape, which I understand is not
allowed because of virus/trojan concerns. I was hoping there was a simplier
way.

"Tom Ogilvy" wrote in message
...
Your original post wasn't visible when I answered your followup. My
answer
was given from the perspective of selection a shape.

For clicking on a shape:
You can assign a single macro to each shape.

write a macro

Sub Line_Click()
Dim s as String, ln as Line
s = Application.Caller
set ln = activesheet.Lines(s)
msgbox ln.Name & " over cell " & ln.TopLeftCell.Address
End Sub


--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
SelectionChange is only triggered by changing the cell selection.
Selecting
an object triggers no worksheet event.

--
Regards,
Tom Ogilvy

"Ray Pixley" wrote in message
news:C8%Zf.1683$8g3.680@trnddc02...
That's not what I had in mind. I put your code into the VBA code
associated
with a blank spreadsheet and added the Worksheet_SelectionChange() code
on
the same sheet as follows:

Public Sub WhichShape()
Dim strSelectedShape As String
On Error GoTo NONAME
strSelectedShape = Selection.Name
MsgBox strSelectedShape
NONAME:
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox "targetaddress is " & Target.AddressLocal
End Sub

Then I placed some arrows on the sheet, to provide a shape to select.

When I select a cell, I get the message box stating its address. When
I
select a line, nothing happens. Why does one work, and not the other?



"Ken Johnson" wrote in message
oups.com...
Hi Ray,
If a shape is selected then Shape.Name returns its name.
If the selection is a named range then Shape.Name returns the
RefersTo
eg "=Sheet1!$A$1"
If the selection is an unnamed range then an error occurs.
So if your sheet has no named ranges you could use something like...

Public Sub WhichShape()
Dim strSelectedShape As String
On Error GoTo NONAME
strSelectedShape = Selection.Name
MsgBox strSelectedShape
NONAME:
End Sub

If there is a named range(s) on the sheet then you might have to test
for the presence of the "=" in the string strSelectedShape...

Public Sub WhichShape()
Dim strSelectedShape As String
On Error GoTo NONAME
strSelectedShape = Selection.Name
If Left(strSelectedShape, 1) < "=" Then
MsgBox strSelectedShape
End If
NONAME:
End Sub

Ken Johnson

Both your assumptions are completely wrong.

First I said
You can assign a single macro to each shape

guess that wasn't clear: Here is an attempt at being clearer.

You can assign the single sample macro to all your shapes. So you can have
1000 shapes and one single macro. This single macro can be assigned to each
of the 1000 shapes. Replace 1000 with the actual number of shapes you have.

There is nothing in the code that assumes anything about the the name of the
shape. It does assume the shape is a line object as you stated. Therefore
it is in the lines collection. The name of the shape is determined
dynamically.

--
Regards,
Tom Ogilvy



"Ray Pixley" wrote in message
news:pQe_f.2189$wH1.223@trnddc03...
So if I have 500 lines, I have to write 500 macros? I could, but that
sounds like overkill, and probably won't work. While I thought this info
was
unnecessary to answer the question, you are probably assuming the line
name
has the string "Line" in them and are sequentially numbered. They don't
and
they won't be. Their names will not be predictable when writing the
macro.
Consequently, to do as you suggest, I need to have a macro write a macro
or
some way of "late-binding" a macro to a shape, which I understand is not
allowed because of virus/trojan concerns. I was hoping there was a
simplier
way.

"Tom Ogilvy" wrote in message
...
Your original post wasn't visible when I answered your followup. My
answer
was given from the perspective of selection a shape.

For clicking on a shape:
You can assign a single macro to each shape.

write a macro

Sub Line_Click()
Dim s as String, ln as Line
s = Application.Caller
set ln = activesheet.Lines(s)
msgbox ln.Name & " over cell " & ln.TopLeftCell.Address
End Sub


--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
SelectionChange is only triggered by changing the cell selection.
Selecting
an object triggers no worksheet event.

--
Regards,
Tom Ogilvy

"Ray Pixley" wrote in message
news:C8%Zf.1683$8g3.680@trnddc02...
That's not what I had in mind. I put your code into the VBA code
associated
with a blank spreadsheet and added the Worksheet_SelectionChange()
code
on
the same sheet as follows:

Public Sub WhichShape()
Dim strSelectedShape As String
On Error GoTo NONAME
strSelectedShape = Selection.Name
MsgBox strSelectedShape
NONAME:
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox "targetaddress is " & Target.AddressLocal
End Sub

Then I placed some arrows on the sheet, to provide a shape to select.

When I select a cell, I get the message box stating its address.
When
I
select a line, nothing happens. Why does one work, and not the
other?



"Ken Johnson" wrote in message
oups.com...
Hi Ray,
If a shape is selected then Shape.Name returns its name.
If the selection is a named range then Shape.Name returns the
RefersTo
eg "=Sheet1!$A$1"
If the selection is an unnamed range then an error occurs.
So if your sheet has no named ranges you could use something
like...

Public Sub WhichShape()
Dim strSelectedShape As String
On Error GoTo NONAME
strSelectedShape = Selection.Name
MsgBox strSelectedShape
NONAME:
End Sub

If there is a named range(s) on the sheet then you might have to
test
for the presence of the "=" in the string strSelectedShape...

Public Sub WhichShape()
Dim strSelectedShape As String
On Error GoTo NONAME
strSelectedShape = Selection.Name
If Left(strSelectedShape, 1) < "=" Then
MsgBox strSelectedShape
End If
NONAME:
End Sub

Ken Johnson

Sorry. Sometimes I can be dense.

I tried your code again, but couldn't get a response when I selected a line.
I then had it called from sub Worksheet_SelectionChange and got a run type
error 13 with the "s=Application.Caller" line highlighted. I tried stepping
it (F8 ing) and got "Error 2023" on the same line. I changed the S to
variant, and got "Run time error 1004". I probably doing something wrong,
but have no clue.

The code is in the VBA window associated with Sheet1, where I have the test
lines. Just to make sure we are looking at the same thing, the following is
what I tried:

Option Explicit
Sub Line_Click()
Dim s As Variant, ln As Line
s = Application.Caller
Set ln = ActiveSheet.Lines(s)
MsgBox ln.Name & " over cell " & ln.TopLeftCell.Address
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Line_Click
End Sub



"Tom Ogilvy" wrote in message
...
Both your assumptions are completely wrong.

First I said
You can assign a single macro to each shape

guess that wasn't clear: Here is an attempt at being clearer.

You can assign the single sample macro to all your shapes. So you can
have
1000 shapes and one single macro. This single macro can be assigned to
each
of the 1000 shapes. Replace 1000 with the actual number of shapes you
have.

There is nothing in the code that assumes anything about the the name of
the
shape. It does assume the shape is a line object as you stated.
Therefore
it is in the lines collection. The name of the shape is determined
dynamically.

--
Regards,
Tom Ogilvy



"Ray Pixley" wrote in message
news:pQe_f.2189$wH1.223@trnddc03...
So if I have 500 lines, I have to write 500 macros? I could, but that
sounds like overkill, and probably won't work. While I thought this info
was
unnecessary to answer the question, you are probably assuming the line
name
has the string "Line" in them and are sequentially numbered. They don't
and
they won't be. Their names will not be predictable when writing the
macro.
Consequently, to do as you suggest, I need to have a macro write a macro
or
some way of "late-binding" a macro to a shape, which I understand is not
allowed because of virus/trojan concerns. I was hoping there was a
simplier
way.

"Tom Ogilvy" wrote in message
...
Your original post wasn't visible when I answered your followup. My
answer
was given from the perspective of selection a shape.

For clicking on a shape:
You can assign a single macro to each shape.

write a macro

Sub Line_Click()
Dim s as String, ln as Line
s = Application.Caller
set ln = activesheet.Lines(s)
msgbox ln.Name & " over cell " & ln.TopLeftCell.Address
End Sub


--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
SelectionChange is only triggered by changing the cell selection.
Selecting
an object triggers no worksheet event.

--
Regards,
Tom Ogilvy

"Ray Pixley" wrote in message
news:C8%Zf.1683$8g3.680@trnddc02...
That's not what I had in mind. I put your code into the VBA code
associated
with a blank spreadsheet and added the Worksheet_SelectionChange()
code
on
the same sheet as follows:

Public Sub WhichShape()
Dim strSelectedShape As String
On Error GoTo NONAME
strSelectedShape = Selection.Name
MsgBox strSelectedShape
NONAME:
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox "targetaddress is " & Target.AddressLocal
End Sub

Then I placed some arrows on the sheet, to provide a shape to
select.

When I select a cell, I get the message box stating its address.
When
I
select a line, nothing happens. Why does one work, and not the
other?



"Ken Johnson" wrote in message
oups.com...
Hi Ray,
If a shape is selected then Shape.Name returns its name.
If the selection is a named range then Shape.Name returns the
RefersTo
eg "=Sheet1!$A$1"
If the selection is an unnamed range then an error occurs.
So if your sheet has no named ranges you could use something
like...

Public Sub WhichShape()
Dim strSelectedShape As String
On Error GoTo NONAME
strSelectedShape = Selection.Name
MsgBox strSelectedShape
NONAME:
End Sub

If there is a named range(s) on the sheet then you might have to
test
for the presence of the "=" in the string strSelectedShape...

Public Sub WhichShape()
Dim strSelectedShape As String
On Error GoTo NONAME
strSelectedShape = Selection.Name
If Left(strSelectedShape, 1) < "=" Then
MsgBox strSelectedShape
End If
NONAME:
End Sub

Ken Johnson

Selectionchange has no role here.

You move the code to a general module, NOT any sheet or the this workbook
module

You then right click on each shape and select Assign Macro from the popup
menu. You select this macro.

Now click on the Line.

Repeat for each shape. (just the assignment part.)

Calling the macro in any other way except by assigning it to the shape and
clicking on the shape will raise an error with Application.Caller because it
wouldn't be set to anything in any other context.

If you want a sample workbook, contact me at

--
Regards,
Tom Ogilvy


"Ray Pixley" wrote:

Sorry. Sometimes I can be dense.

I tried your code again, but couldn't get a response when I selected a line.
I then had it called from sub Worksheet_SelectionChange and got a run type
error 13 with the "s=Application.Caller" line highlighted. I tried stepping
it (F8 ing) and got "Error 2023" on the same line. I changed the S to
variant, and got "Run time error 1004". I probably doing something wrong,
but have no clue.

The code is in the VBA window associated with Sheet1, where I have the test
lines. Just to make sure we are looking at the same thing, the following is
what I tried:

Option Explicit
Sub Line_Click()
Dim s As Variant, ln As Line
s = Application.Caller
Set ln = ActiveSheet.Lines(s)
MsgBox ln.Name & " over cell " & ln.TopLeftCell.Address
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Line_Click
End Sub



"Tom Ogilvy" wrote in message
...
Both your assumptions are completely wrong.

First I said
You can assign a single macro to each shape

guess that wasn't clear: Here is an attempt at being clearer.

You can assign the single sample macro to all your shapes. So you can
have
1000 shapes and one single macro. This single macro can be assigned to
each
of the 1000 shapes. Replace 1000 with the actual number of shapes you
have.

There is nothing in the code that assumes anything about the the name of
the
shape. It does assume the shape is a line object as you stated.
Therefore
it is in the lines collection. The name of the shape is determined
dynamically.

--
Regards,
Tom Ogilvy



"Ray Pixley" wrote in message
news:pQe_f.2189$wH1.223@trnddc03...
So if I have 500 lines, I have to write 500 macros? I could, but that
sounds like overkill, and probably won't work. While I thought this info
was
unnecessary to answer the question, you are probably assuming the line
name
has the string "Line" in them and are sequentially numbered. They don't
and
they won't be. Their names will not be predictable when writing the
macro.
Consequently, to do as you suggest, I need to have a macro write a macro
or
some way of "late-binding" a macro to a shape, which I understand is not
allowed because of virus/trojan concerns. I was hoping there was a
simplier
way.

"Tom Ogilvy" wrote in message
...
Your original post wasn't visible when I answered your followup. My
answer
was given from the perspective of selection a shape.

For clicking on a shape:
You can assign a single macro to each shape.

write a macro

Sub Line_Click()
Dim s as String, ln as Line
s = Application.Caller
set ln = activesheet.Lines(s)
msgbox ln.Name & " over cell " & ln.TopLeftCell.Address
End Sub


--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
SelectionChange is only triggered by changing the cell selection.
Selecting
an object triggers no worksheet event.

--
Regards,
Tom Ogilvy

"Ray Pixley" wrote in message
news:C8%Zf.1683$8g3.680@trnddc02...
That's not what I had in mind. I put your code into the VBA code
associated
with a blank spreadsheet and added the Worksheet_SelectionChange()
code
on
the same sheet as follows:

Public Sub WhichShape()
Dim strSelectedShape As String
On Error GoTo NONAME
strSelectedShape = Selection.Name
MsgBox strSelectedShape
NONAME:
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox "targetaddress is " & Target.AddressLocal
End Sub

Then I placed some arrows on the sheet, to provide a shape to
select.

When I select a cell, I get the message box stating its address.
When
I
select a line, nothing happens. Why does one work, and not the
other?



"Ken Johnson" wrote in message
oups.com...
Hi Ray,
If a shape is selected then Shape.Name returns its name.
If the selection is a named range then Shape.Name returns the
RefersTo
eg "=Sheet1!$A$1"
If the selection is an unnamed range then an error occurs.
So if your sheet has no named ranges you could use something
like...

Public Sub WhichShape()
Dim strSelectedShape As String
On Error GoTo NONAME
strSelectedShape = Selection.Name
MsgBox strSelectedShape
NONAME:
End Sub

If there is a named range(s) on the sheet then you might have to
test
for the presence of the "=" in the string strSelectedShape...

Public Sub WhichShape()
Dim strSelectedShape As String
On Error GoTo NONAME
strSelectedShape = Selection.Name
If Left(strSelectedShape, 1) < "=" Then
MsgBox strSelectedShape
End If
NONAME:
End Sub

Ken Johnson

Now it works. (Sorry for taking so long to get back.)


"Tom Ogilvy" wrote in message
...
Selectionchange has no role here.

You move the code to a general module, NOT any sheet or the this workbook
module

You then right click on each shape and select Assign Macro from the popup
menu. You select this macro.

Now click on the Line.

Repeat for each shape. (just the assignment part.)

Calling the macro in any other way except by assigning it to the shape and
clicking on the shape will raise an error with Application.Caller because
it
wouldn't be set to anything in any other context.

If you want a sample workbook, contact me at

--
Regards,
Tom Ogilvy


"Ray Pixley" wrote:

Sorry. Sometimes I can be dense.

I tried your code again, but couldn't get a response when I selected a
line.
I then had it called from sub Worksheet_SelectionChange and got a run
type
error 13 with the "s=Application.Caller" line highlighted. I tried
stepping
it (F8 ing) and got "Error 2023" on the same line. I changed the S to
variant, and got "Run time error 1004". I probably doing something
wrong,
but have no clue.

The code is in the VBA window associated with Sheet1, where I have the
test
lines. Just to make sure we are looking at the same thing, the following
is
what I tried:

Option Explicit
Sub Line_Click()
Dim s As Variant, ln As Line
s = Application.Caller
Set ln = ActiveSheet.Lines(s)
MsgBox ln.Name & " over cell " & ln.TopLeftCell.Address
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Line_Click
End Sub



"Tom Ogilvy" wrote in message
...
Both your assumptions are completely wrong.

First I said
You can assign a single macro to each shape

guess that wasn't clear: Here is an attempt at being clearer.

You can assign the single sample macro to all your shapes. So you can
have
1000 shapes and one single macro. This single macro can be assigned to
each
of the 1000 shapes. Replace 1000 with the actual number of shapes you
have.

There is nothing in the code that assumes anything about the the name
of
the
shape. It does assume the shape is a line object as you stated.
Therefore
it is in the lines collection. The name of the shape is determined
dynamically.

--
Regards,
Tom Ogilvy



"Ray Pixley" wrote in message
news:pQe_f.2189$wH1.223@trnddc03...
So if I have 500 lines, I have to write 500 macros? I could, but that
sounds like overkill, and probably won't work. While I thought this
info
was
unnecessary to answer the question, you are probably assuming the line
name
has the string "Line" in them and are sequentially numbered. They
don't
and
they won't be. Their names will not be predictable when writing the
macro.
Consequently, to do as you suggest, I need to have a macro write a
macro
or
some way of "late-binding" a macro to a shape, which I understand is
not
allowed because of virus/trojan concerns. I was hoping there was a
simplier
way.

"Tom Ogilvy" wrote in message
...
Your original post wasn't visible when I answered your followup.
My
answer
was given from the perspective of selection a shape.

For clicking on a shape:
You can assign a single macro to each shape.

write a macro

Sub Line_Click()
Dim s as String, ln as Line
s = Application.Caller
set ln = activesheet.Lines(s)
msgbox ln.Name & " over cell " & ln.TopLeftCell.Address
End Sub


--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
SelectionChange is only triggered by changing the cell selection.
Selecting
an object triggers no worksheet event.

--
Regards,
Tom Ogilvy

"Ray Pixley" wrote in message
news:C8%Zf.1683$8g3.680@trnddc02...
That's not what I had in mind. I put your code into the VBA code
associated
with a blank spreadsheet and added the
Worksheet_SelectionChange()
code
on
the same sheet as follows:

Public Sub WhichShape()
Dim strSelectedShape As String
On Error GoTo NONAME
strSelectedShape = Selection.Name
MsgBox strSelectedShape
NONAME:
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox "targetaddress is " & Target.AddressLocal
End Sub

Then I placed some arrows on the sheet, to provide a shape to
select.

When I select a cell, I get the message box stating its address.
When
I
select a line, nothing happens. Why does one work, and not the
other?



"Ken Johnson" wrote in message
oups.com...
Hi Ray,
If a shape is selected then Shape.Name returns its name.
If the selection is a named range then Shape.Name returns the
RefersTo
eg "=Sheet1!$A$1"
If the selection is an unnamed range then an error occurs.
So if your sheet has no named ranges you could use something
like...

Public Sub WhichShape()
Dim strSelectedShape As String
On Error GoTo NONAME
strSelectedShape = Selection.Name
MsgBox strSelectedShape
NONAME:
End Sub

If there is a named range(s) on the sheet then you might have
to
test
for the presence of the "=" in the string strSelectedShape...

Public Sub WhichShape()
Dim strSelectedShape As String
On Error GoTo NONAME
strSelectedShape = Selection.Name
If Left(strSelectedShape, 1) < "=" Then
MsgBox strSelectedShape
End If
NONAME:
End Sub

Ken Johnson