Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference oleobject within shape group
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference oleobject within shape group
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference oleobject within shape group
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 wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference oleobject within shape group
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference oleobject within shape group
Stephen,
That's great. I've never found a use for the locals window before so that was really interesting. Cheers, Andrew Stephen Bullen wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
my curser changed from arrow shape to a cross shape???? | New Users to Excel | |||
Deleting a shape and the cell contents the shape is in. | Excel Programming | |||
Need to cast OleObject to CombBbox | Excel Programming | |||
OLEobject on the sheet | Excel Programming | |||
Using a variable to represent a Checkbox Name(OLEObject) | Excel Programming |