I've formatted a worksheet to represent an office plan. I have shapes and
grouped shapes to represent assetts
What I want is to click on a shape, returning the name of the shape selected.
I would use this name to get associated information from a database
spreadsheet.
There isn't any property 'Selected' or 'Activated' associated with the Shape
object.
Anyone know how to do this? Any response would be much appreciated.
Using Excel2002 (10.2614.2625)
O/S XP

Dim s As Shape
Set s = ActiveSheet.Shapes(Application.Caller)

In the macro assigned to the Shape, this is the Shape clicked. For example:


Sub shpname()
Dim s As Shape
Set s = ActiveSheet.Shapes(Application.Caller)
MsgBox (s.Name)
End Sub

--
Gary''s Student - gsnu200720


"AndyT" wrote:

I've formatted a worksheet to represent an office plan. I have shapes and
grouped shapes to represent assetts
What I want is to click on a shape, returning the name of the shape selected.
I would use this name to get associated information from a database
spreadsheet.
There isn't any property 'Selected' or 'Activated' associated with the Shape
object.
Anyone know how to do this? Any response would be much appreciated.
Using Excel2002 (10.2614.2625)
O/S XP

Try:

Dim sObj As Object
Set sObj = Selection
MsgBox sObj.Name

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"AndyT" wrote in message
...
I've formatted a worksheet to represent an office plan. I have shapes and
grouped shapes to represent assetts
What I want is to click on a shape, returning the name of the shape
selected.
I would use this name to get associated information from a database
spreadsheet.
There isn't any property 'Selected' or 'Activated' associated with the
Shape
object.
Anyone know how to do this? Any response would be much appreciated.
Using Excel2002 (10.2614.2625)
O/S XP

This works in all but one respect. The name returned is the underlying
'autoshape###' format name & not the 'Desk ##' format name that I named the
group. The way the name box works is what I'm looking for, when I click on a
shape or group of shapes, the name I've given to the objects appears.
Thanks for your help
AndyT
"Gary''s Student" wrote:

Dim s As Shape
Set s = ActiveSheet.Shapes(Application.Caller)

In the macro assigned to the Shape, this is the Shape clicked. For example:


Sub shpname()
Dim s As Shape
Set s = ActiveSheet.Shapes(Application.Caller)
MsgBox (s.Name)
End Sub

--
Gary''s Student - gsnu200720


"AndyT" wrote:

I've formatted a worksheet to represent an office plan. I have shapes and
grouped shapes to represent assetts
What I want is to click on a shape, returning the name of the shape selected.
I would use this name to get associated information from a database
spreadsheet.
There isn't any property 'Selected' or 'Activated' associated with the Shape
object.
Anyone know how to do this? Any response would be much appreciated.
Using Excel2002 (10.2614.2625)
O/S XP

To test, I have assigned macros to 3 shapes, when I click on one of these
shapes I'm not able to select it, naturally, it runs the macro.
If I select one of the shapes that have not been assigned to the macro, it
becomes selected (obviously). If I then click an assigned shape it returns
the name of the unassigned shape and not the one clicked.
When I have assigned macros to all the shapes I will not be able to select
and if no shape is selected when I click an assigned shape I get an
'Application-defined or object-defined error'.
On the bright side, I can use this code a different way. Namely, by
assigning the code to a command button, clicking and selecting any of the
shapes then the command button this would return the name as needed.
Nevertheless, I would prefer the way I thought of origially if it can be
achieved.
Thanks for your help
AndyT

"Bob Flanagan" wrote:

Try:

Dim sObj As Object
Set sObj = Selection
MsgBox sObj.Name

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"AndyT" wrote in message
...
I've formatted a worksheet to represent an office plan. I have shapes and
grouped shapes to represent assetts
What I want is to click on a shape, returning the name of the shape
selected.
I would use this name to get associated information from a database
spreadsheet.
There isn't any property 'Selected' or 'Activated' associated with the
Shape
object.
Anyone know how to do this? Any response would be much appreciated.
Using Excel2002 (10.2614.2625)
O/S XP

AndyT,

Here is a function I developed to test the Selection. It's non-
production as of now, so I haven't fully tested it.

==================================================
Public Function IsSelectionAShape() As Boolean

Dim isShape As Boolean
Dim sType As String

sType = TypeName(Application.Selection)

' eliminate the obvious / most frequent case
If sType < "Range" Then
Select Case sType
' Delete any you don't want to test for
Case "Arc": isShape = True ' autoshape -
special case
Case "Drawing": isShape = True ' freeform or
scribble
Case "DrawingObjects": isShape = True ' grouped shapes
Case "GroupObject": isShape = True ' diagram - org
chart, target, radial, etc.
Case "Line": isShape = True ' line or arrow
(non-Connector)
Case "OLEObject": isShape = True ' on sheet VB
control
Case "Oval": isShape = True ' autoshape -
special case
Case "Picture": isShape = True ' picture
Case "Rectangle": isShape = True ' applies to most
shapes
Case "TextBox": isShape = True ' textbox or
visible cell comment
Case "ChartObject": isShape = True ' chart
Case Else: isShape = False
End Select
End If
IsSelectionAShape = isShape

End Function
==================================================


HTH,

Nicholas Hebb
BreezeTree Software
http://www.breezetree.com

This isn't what I was looking for, but I can understand, considering my
original problem statement why you were misled. The code tests 'what type of
object has been selected' instead of 'which object has been selected' and
returning the name of that object by selecting the object itself. I hope this
makes sense to you as I carn't think of a better way of putting it. The code
you have offered will be useful further on in this project, so thanks for
that at least and thanks for responding.
AndyT

"Nick Hebb" wrote:

AndyT,

Here is a function I developed to test the Selection. It's non-
production as of now, so I haven't fully tested it.

==================================================
Public Function IsSelectionAShape() As Boolean

Dim isShape As Boolean
Dim sType As String

sType = TypeName(Application.Selection)

' eliminate the obvious / most frequent case
If sType < "Range" Then
Select Case sType
' Delete any you don't want to test for
Case "Arc": isShape = True ' autoshape -
special case
Case "Drawing": isShape = True ' freeform or
scribble
Case "DrawingObjects": isShape = True ' grouped shapes
Case "GroupObject": isShape = True ' diagram - org
chart, target, radial, etc.
Case "Line": isShape = True ' line or arrow
(non-Connector)
Case "OLEObject": isShape = True ' on sheet VB
control
Case "Oval": isShape = True ' autoshape -
special case
Case "Picture": isShape = True ' picture
Case "Rectangle": isShape = True ' applies to most
shapes
Case "TextBox": isShape = True ' textbox or
visible cell comment
Case "ChartObject": isShape = True ' chart
Case Else: isShape = False
End Select
End If
IsSelectionAShape = isShape

End Function
==================================================


HTH,

Nicholas Hebb
BreezeTree Software
http://www.breezetree.com