Re: Shape instead of Target

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 = Active***.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" <twogilvy@xxxxxxx> wrote in message
news:OBf9rkDXGHA.3864@xxxxxxxxxxxxxxxxxxxxxxx
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" <r.pixley@xxxxxxxxxxx> wrote in message
news:pQe_f.2189$wH1.223@xxxxxxxxxxx
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













.