Edit

Share via

OLEObjects object (Excel)

  • Article

A collection of all the OLEObject objects on the specified worksheet.

Remarks

Each OLEObject object represents an ActiveX control or a linked or embedded OLE object.

An ActiveX control on a sheet 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, and the code name for the control, which you can see in the cell to the right of (Name) in the Properties window.

When you first add a control to a sheet, 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. The latter however, seems to have changed with Excel versions. With version 16.0 both are kept consistent and is not possible to change one of the two alone.

Example

Use the OLEObjects method of the Worksheet object to return the OLEObjects collection.

The following example hides all the OLE objects on worksheet one.

Worksheets(1).OLEObjects.Visible = False

Use the Add method to create a new OLE object and add it to the OLEObjects collection. The following example creates a new OLE object representing the bitmap file Arcade.bmp and adds it to worksheet one.

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

The following example creates a new ActiveX control (a list box) and adds it to worksheet one.

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

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.

Private Sub chkFinished_Click() 
 ActiveSheet.OLEObjects("CheckBox1").Object.Value = 1 
End Sub

Methods

Properties

See also

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.