Microsoft Excel - vba 1004 error with renamed shapes object

Asked By auto q on 08-Oct-09 02:48 PM

I have 2 autoshapes which are in a group.
When an item of the group is clicked I want an action to happen.
e.g. I assign a macro to the object which does the following

Public Sub processClick()
Dim sr As Shape
clickPlace = Application.Caller

MsgBox ("clicked by " + clickPlace)
Set aSheet = Worksheets("coverSheet")

Set sr = aSheet.Shapes(clickPlace)

Eventually I want to do
MsgBox ("group:" + sr.ParentGroup.Name)

Initially, this appears to be fine and gives a message box saying
"clicked by AutoShape 10593"
and then another saying
"group:Group 10598" ,etc.

However if I programatically change the name of e.g."AutoShape10593" to e.g. "helpShape1", it does give the first message box
"clicked by helpShape1".
but it then gives an error when trying to find the object in the shapes collection.
I get a
"runtime error
The item with the specified name wasn't found"

I get the error on the line in bold above
[Set sr = aSheet.Shapes(clickPlace)]

It appears that programatically changing the name of the shape is causing the object to 'disappear' ????

Help !

[ FWIW if I change the code slightly .e.g
REPLACE THE LINE
Set sr = aSheet.Shapes(clickPlace)
WITH
Set myRange = aSheet.Shapes.Range(clickPlace)

I get a
"runtime error '1004'
The item with the specified name wasn't found"
]
This is still the same sort of error....

many thx in advance !

Jonathan VH replied to auto q on 08-Oct-09 04:36 PM

I don't think this is caused by your rename. I think it was caused by grouping the shapes.

If shapes are in a group, they are no longer members of the worksheet's Shapes collection, but instead are members (GroupItems) of the shape that is the group. This is why you can have shapes with the same names in different groups. This would not be an issue except that Application.Caller returns a string, not the object, when the event is triggered by clicking a shape, and you have to know the shape's ancestors before you can instantiate an object from just the shape's name. A clue to this is that you did not need to Set the clickPlace variable when you instantiated it... because it's not an object.

I.e., your instantiation of your sr variable requires code like this:

Set sr = Activesheet.Shapes("Group 10598").GroupItems(clickPlace)

and you cannot get the shape's group from just its name (remember, you can have multiple shapes with the same names as long as they're in different groups).

I think this means you'll have to code a separate event ("assigned macro") for each shape. You could also ungroup your clickable shapes and use code like you have.

vba 1004 error with renamed shapes object - auto q replied to Jonathan VH on 08-Oct-09 06:04 PM


Jonathan, firstly many thanks for the clear and logical response. I do feel that you are correct, however I am still slightly confused by the fact that the code works perfectly well when  I just use a "standard" autoshape. (i.e. the existing code will allow me to find the group which the object is a member of.)
Its only when I do the "rename" that the code gives the error (?). . its as f the internal object model is not being updated properly and the object is getting "lost" in the group.
Thanks for the suggestion about having a different handler for each object - not exactly what I was hoping for but certainly an option.
Perhaps I can turn the question around a bit:-  If  I know the name of an item in a group (and I have explicitly named that object), how can I find its parent ? ( essentially that is what I am trying to do.. )

I have just stepped through with the debugger and it appears that my renamed object is in the "sheets" object model  i.e.
sheet
-->Item41
     ----> GroupItems
           ----->Item3
                        -------->Name helpShape1

Performance is not a concern for this project, so I am thinking of building an "object map" of my own - iterating through all the "known" objects, right at the beginning of the process before any buttons are pressed.
(just something simple using either arrays, or some hidden cells ,etc). I can then just use my own cut-down object model to get the information which I need.... unless there is some way to use the existing object model ... ?
e.g.

extra = 0
    For abc = 1 To aSheet.Shapes.Count
        ActiveSheet.Range("T" + CStr(5 + abc + extra)).Value = aSheet.Shapes.Item(abc).Name
                    If aSheet.Shapes.Item(abc).Type = msoGroup Then
                        For anItem = 1 To aSheet.Shapes.Item(abc).GroupItems.Count
                            strName = aSheet.Shapes.Item(abc).GroupItems.Item(anItem).Name
                            ActiveSheet.Range("T" + CStr(5 + abc + extra)).Value = "GROUPITEM:" + strName
                            If strName = clickPlace Then
                               MsgBox (" the groupname is " + aSheet.Shapes.Item(abc).GroupItems.Item(anItem).ParentGroup.Name)
                            End If
                            extra = extra + 1
                        Next anItem
                    End If
    Next abc


Again many thanks for your help/response
Jonathan VH replied to auto q on 08-Oct-09 06:27 PM
Well, you can certainly find the ancestors of a shape object from its name if you use unique names. As I wrote, Excel will not guarantee this uniqueness, and you will get unexpected results if you do not somehow enforce this. If your sheets have the possibility of groups containing other groups, you may want to use recursion (note that I have used the word ancestors rather than parent).
auto q replied to Jonathan VH on 09-Oct-09 01:32 AM

Okay, well I will be using unique names (I give all my shapes unique names based on their type right at the beginning  - which appears to be one of the reasons as to why I'm getting this issue :-) ; but thanks to your assistance I have sligtly changed what I am going to do. To cut a long story short,  I'm not getting this error any more :- many thanks again !