OLEObjects

The OLEObjects collection is a collections of all the ActiveX controls or linked or embedded OLE objects


Add Method
Use the Add method to create a new OLE object


ActiveSheet.OLEObjects.Add
ClassType,
Filename,
Link,
DisplayAsIcon,
IconFileName,
IconIndex,
IconLabel,
Left,
Top,
Width,
Height)


ClassType - A string that contains the programmatic identifier for the object to be created (you must specify either ClassType or Filename but not both)
Filename - The file for the object that you want to create (you must specify either ClassType or Filename but not both)
link - True or false indicating whether to link the object
DisplayAsIcon - True or false indicating whether to display the object as an icon
IconFileName - The file containing the icon to be displayed
IconIndex - The index of the icon within the IconFileName
IconLabel - A text caption to display under the icon
Left - The position (in points) of the upper left corner relative to the upper left corner of the document, default is 0.
Top - The position (in points) of the upper left corner relative to the upper left corner of the document, default is 0.
Width - The width of the object (in points)
Height - The height of the object (in points)



ActiveSheet.OLEObjects.Add(Filename:= _
"C:\Users\proctorr\Desktop\807b93399950447eade5fca69ba898d8.docx", Link:= _
False, DisplayAsIcon:=False).Select


Worksheets(1).OLEObjects.Add FileName:="arcade.gif"
Worksheets(1).OLEObjects.Add ClassType:="Forms.ListBox.1"


Worksheets("Sheet1").OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, DisplayAsIcon:=False, Left:=300, Top:=63, Width:=50.5, Height:=50)



An ActiveX control on a worksheet has two names:
the name of the shape that contains the control, which you can see in the Name box when you view the sheet,
the code name for the control, which you can see in the cell to the right of (Name) in the Properties window.
When you add a control to a worksheet, the shape name and code name match.


However, if you change either the shape name or code name, the other is not automatically changed to match.
You use the code name of a control in the names of its event procedures.
However, when you return a control from the Shapes or OLEObjects collection for a sheet, you must use the shape name, not the code name, to refer to the control by name.
For example, assume that you add a check box to a sheet and that both the default shape name and the default code name are CheckBox1.


If you then change the control code name by typing chkFinished next to (Name) in the Properties window, you must use chkFinished in event procedures names, but you still have to use CheckBox1 to return the control from the Shapes or OLEObject collection, as shown in the following example.


ActiveSheet.OLEObjects("CheckBox1").Object.Value = 1



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