Worksheets(1).OLEObjects("CommandButton1"). The following example sets the caption for CommandButton1. The following example sets the left position of the control named "CommandButton1." Worksheets(1).OLEObjects("CommandButton1").Left = 10Ĭontrol properties that are not shown as properties of the OLEObject object can be set by returning the actual control object using the Object property. To change the control name you use in Visual Basic code, select the control and set the (Name) property in the Properties window.īecause ActiveX controls are also represented by OLEObject objects in the OLEObjects collection, you can set control properties using the objects in the collection. Note that when you use a control name outside the class module for the sheet containing the control, you must qualify the control name with the sheet name. The following example changes the caption on the control named "CommandButton1." = "Run"
Most often, your Visual Basic code will refer to ActiveX controls by name. Using Control Properties with Visual Basic Worksheets(1).OLEObjects.Add "Forms.CommandButton.1", _ The following example adds a command button to worksheet 1. To programmatically add an ActiveX control to a sheet, use the Add method of the OLEObjects collection. In Microsoft Excel, ActiveX controls are represented by OLEObject objects in the OLEObjects collection (all OLEObject objects are also in the Shapes collection). The Me keyword in an event procedure for an ActiveX control on a sheet refers to the sheet, not to the control.
When a Microsoft Excel workbook is saved using the Microsoft Excel 5.0/95 Workbook file format, ActiveX control information is lost. The controls will work if the user right-clicks the workbook and selects the Open command from the shortcut menu. For example, the following code sorts the range: Private Sub CommandButton1.ClickĬontrols on a Microsoft Excel workbook embedded in a document in another application will not work if the user double-clicks the workbook to edit it. You can work around this problem by activating some other element on the sheet before you use the property or method that failed. For example, the Sort method cannot be used when a control is active, so the following code fails in a button click event procedure (because the control is still active after the user clicks it). Some Microsoft Excel Visual Basic methods and properties are disabled when an ActiveX control is activated. The following example scrolls the workbook window so CommandButton1 is in the upper-left corner. These properties can be set and returned using the ActiveX control name.
In addition to the standard properties available for ActiveX controls, the following properties can be used with ActiveX controls in Microsoft Excel: BottomRightCell, LinkedCell, ListFillRange, Placement, PrintObject, TopLeftCell, and ZOrder. Keep the following points in mind when you are working with controls on sheets: For general information on adding and working with controls, see Using ActiveX Controls on a Document and Creating a Custom Dialog Box.
This topic covers specific information about using ActiveX controls on worksheets and chart sheets.