Accessing your Controls Directly

Any object which is embedded on a worksheet appears in the Drawing layer.
There are several ways to access the objects in the Drawing layer:
1) Use the Shapes collection.
2) Use the OLE Objects collection (Control Toolbar controls only).
3) Refer to the specific name of the object (Control Toolbar controls only).
The first two methods are useful if you want to loop through all the controls on a particular worksheet.



Application.Caller

This property can be very useful for finding out which drawing object activated a procedure



Using the Shapes Collection

This method can be used to refer to controls that have been added from the Control Toolbox toolbar.

Dim shShape As Shape 
For Each shShape in ActiveSheet.Shapes
   If shShape.Type = msoOLEControlObject Then
      shShape.Select
   End If
Next shShape

Command Button fonts - Perpetua Titling MT, Rockwell, Sylfaen


Shape Object Summary

This table summarises the different types of objects that can be embedded in the Drawing layer

ShapeNo.DescriptionShapeNo.Description
msoAutoShape1 msoLinkedPicture11 
msoCallout2 msoOLEControlObject12 
msoChart3 msoPicture13 
msoComment4 msoPlaceholder14 
msoFreeform5 msoTextEffect15 
msoGroup6 msoMedia16 
msoEmbeddedOLEObject7 msoTextBox17 
msoFormControl8 msoScriptAnchor18 
msoLine9 msoTable19 
msoLinkedOLEObject10 msoShapeTypeMixed-2 

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext