Re: Shape instead of Target

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" <twogilvy@xxxxxxx> wrote in message
news:%23KniW64WGHA.4148@xxxxxxxxxxxxxxxxxxxxxxx
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