Reference oleobject within shape group

[Andrew]

I have a group of shapes which includes an MSForms.SpinButton. I can
reference this using the following:

Dim sh As Shape
Set sh = Sheets("Overview").Shapes("Group 447").GroupItems(2)
Debug.Print sh.Name

However, I want to set the reference to the OLE object not just the
shape (so that I can use WithEvents to trap the change event). Is
there a simple way to do this? Something like:

Dim sh as MSForms.Spinbutton (OR OLEObject)
Set sh = Sheets("Overview").Shapes("Group 447").GroupItems(2)

So far the best I can do is to ungroup the shapes, set the reference
and then re-group them. It works but it seems like there should be a
neater way.

Thanks a lot,
Andrew

 

[Stephen Bullen]

Hi Andrew,

I have a group of shapes which includes an MSForms.SpinButton. I can
reference this using the following:

Dim sh As Shape
Set sh = Sheets("Overview").Shapes("Group 447").GroupItems(2)
Debug.Print sh.Name

However, I want to set the reference to the OLE object not just the
shape (so that I can use WithEvents to trap the change event). Is
there a simple way to do this? Something like:

Dim sh as MSForms.Spinbutton (OR OLEObject)
Set sh = Sheets("Overview").Shapes("Group 447").GroupItems(2)

How about:

Dim sh as MSForms.Spinbutton
Set sh = Sheets("Overview").Shapes("Group 447") _
.GroupItems(2).OLEObject.Object

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk

 

[Andrew]

Stephen,

Thanks for the help. When I run your suggestion I get an error though
("Obj doesn't support this method or property"). I wonder if I'm
stuck here because GroupItems seems to only return a shape object.
Let me know if you've got any other ideas though.

Thanks,
Andrew

 

[Stephen Bullen]

Hi Andrew,

Thanks for the help. When I run your suggestion I get an error though
("Obj doesn't support this method or property"). I wonder if I'm
stuck here because GroupItems seems to only return a shape object.
Let me know if you've got any other ideas though.

Sorry, I mistyped:

Dim oShp As Shape
Dim oSpn As msforms.SpinButton

Set oShp = ActiveSheet.Shapes(1)
Set oSpn = oShp.GroupItems(2).OLEFormat.Object.Object

The easiest way to work out the reference path is to put a breakpoint
after setting the shape object reference, then display the Locals window
in the IDE and expand out the oShp variable - examining each GroupItem,
OLEFormat and Object property until you find the one of Type
'Object/SpinButton'.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk

 

[Andrew]

Stephen,

That's great. I've never found a use for the locals window before so
that was really interesting.

Cheers,
Andrew