Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Oct 2007
    Posts
    172
    Thanks
    0
    Thanked 2 Times in 1 Post

    OnAction and Shapes (Excel 2003)

    Hi,

    I have some code which automatically creates a shape group and sets the name uniquely so that it can later be found easily. I want to be able to then click on this shape and show a dialog containing data contained within text boxes which form part of this grouped shape.

    I had originally done this by setting the "onAction" property of the shape group and provided the name of a function to call when the shape is clicked. I had hoped that the Application.Caller attribute could be used to determine the name of the shape that had been clicked.

    Unfortunately, the application.Caller attribute contains the name of one of the shapes within the shape group. I can understand that this is the (sub)shape which is actually clicked, but this doesn't help me to identify the name of the shape group itself. I need the name of the shape group because it is the unique identifier with which I am working. In fact, because Application.Caller attribute contains the name of a shape within a shape group, it is not found when using ActiveSheet.Shapes(Application.Caller), which throws an error.

    Can anyone assist me in suggesting how I can retrieve the name of the shape group on which I click within the spreadsheet?

    cheers,
    Dom

  2. #2
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: OnAction and Shapes (Excel 2003)

    Sub HoneyWhatsYourName()
    'Jim Cone - Portland Oregon - Dec 2008
    'Identifies Group name for grouped shapes.
    Dim x As Long
    Dim N As Long
    Dim Shp As Shape
    Dim strname As String
    strname = Application.Caller
    For Each Shp In ActiveSheet.Shapes
    If Shp.Type = msoGroup Then
    x = Shp.GroupItems.Count
    For N = 1 To x
    If Shp.GroupItems(N).Name = strname Then
    MsgBox Shp.Name
    Exit For
    End If
    Next
    End If
    Next
    Set Shp = Nothing
    End Sub

  3. #3
    2 Star Lounger
    Join Date
    Oct 2007
    Posts
    172
    Thanks
    0
    Thanked 2 Times in 1 Post

    Re: OnAction and Shapes (Excel 2003)

    Hi Jim,

    thanks for the suggestion - in your example, all the group-type shapes in the activesheet are searched for a sub-shape with the name of the application caller. This would work in the case where the sub-shape's name is unique. Unfortunately in my case, every group is made up of a set of sub-shapes - each sub-shape name is unique within the group, but every group is the same, meaning that the sub-shape name is not unique overall. Your suggestion would therefore not work in my case because it would return the FIRST group which contained a sub-shape with the same name as the Application.Caller. (the reason why all groups contain the same sub-shapes is because they are all duplicates of a "template" shape.

    Anyway, this whole thing has led me to the conclusion that all the sub-shapes need to be uniquely named within the activesheet using an index number or unique prefix to identify them later. In fact, I know this works, but it requires extra code

  4. #4
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: OnAction and Shapes (Excel 2003)

    <code>Application.Caller</code> returns the name of the caller, not a pointer to the object. So if there are two objects with the same name you will get this problem. I don't think that any code would be able to help you.

    StuartR

  5. #5
    2 Star Lounger
    Join Date
    Oct 2007
    Posts
    172
    Thanks
    0
    Thanked 2 Times in 1 Post

    Re: OnAction and Shapes (Excel 2003)

    Thanks Stuart - my code works now - I just changed it so when the new group object is created by duplicating a "template" group, I just uniquely rename the sub-shapes in which I am interested by using a standard prefix and an index number.

    cheers,

    Dom

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •