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