Re: Shape instead of Target

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 = active***.Lines(s)
msgbox ln.Name & " over cell " & ln.TopLeftCell.Address
End Sub


--
Regards,
Tom Ogilvy


"Tom Ogilvy" <twogilvy@xxxxxxx> wrote in message
news:e$4guu4WGHA.3684@xxxxxxxxxxxxxxxxxxxxxxx
SelectionChange is only triggered by changing the cell selection.
Selecting
an object triggers no work*** event.

--
Regards,
Tom Ogilvy

"Ray Pixley" <r.pixley@xxxxxxxxxxx> wrote in message
news:C8%Zf.1683$8g3.680@xxxxxxxxxxx
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 *** 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 ***, 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" <KenCJohnson@xxxxxxxxx> wrote in message
news:1144544251.300581.181410@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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 *** 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 *** 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







.

Loading