In this article, you will learn how to use ActiveX controls in Excel, step by step. You’ll be relieved to hear that adding ActiveX controls in a worksheet is easy, but it is after you have learned few basic things about how to use them.
This article is part of my series: Excel VBA & Macros – A Step by Step Complete Guide.
Adding an ActiveX control on the worksheet
To add a control to a worksheet, choose Developer ➪ Controls ➪ Insert. From the Insert drop-down list, click any control listed under ActiveX, and then click in your worksheet to create the control. Phew! You don’t need to worry about the exact size or position of your created control because you can modify those properties at any time.
Excel sets itself in Design mode when you add a control to your worksheet. In Design mode, you can change the properties of any controls you have created on your worksheet. You can also add or edit macros for the control and change the control’s size or position.
Note: The Design Mode icon in the Developer ➪ Controls group appears highlighted when Excel is in Design mode. Click this button to toggle Design mode between on and off.
When Excel is in Design mode, you can’t work with the controls. To test whether your controls work properly, you have to exit the Design mode. Just click on the highlighted Design Mode icon to exit Design mode. Many times you will need to change the state of the Design Mode( from on to off or vice versa) when you are working with ActiveX controls in your worksheet.
Changing/ Adjusting properties
Whatever control we add to our worksheet, has various properties that determine how it looks and works. If Excel is in Design mode, only then you can think of changing/adjusting the properties of controls. When we create control in a worksheet, Excel enters in Design mode automatically.
To change the properties for control, follow the steps below:
- At first, you have to make sure that Excel is in Design mode.
- Click the control whose properties you want to adjust. The control will be selected.
- Now click the Properties icon in the Controls section of the Developer tab. The Properties window appears as shown in the figure below.
- Select the property that needs change/adjustment and make the change.
You will find that some properties show the value for them in a drop-down list from which you can select a value for the property. Some properties (such as Font), have a button. Click the button, you will get a dialog box to change the property.
Other properties require you to type in the property values. The changes in property values show an immediate effect.
The Properties window has two tabs. The Alphabetic tab shows the properties in alphabetical order. The Categorized tab displays the properties by category. Both tabs show the same properties; only the order is different.
Each control has its own unique set of properties. However, many controls have common properties. In this section, I have covered some properties that are common to all or many controls.
TABLE: Common Properties in Multiple Controls
|BackColor||The background color of the control.|
|BackStyle||The style of the background. It may be either transparent or opaque.|
|AutoSize||If AutoSize is True, the control resizes itself automatically, based on the text in its caption.|
|Caption||The text that appears on the control.|
|LinkedCell||A worksheet cell that contains the current value of a control.|
|ListFillRange||A worksheet range. It contains items displayed in a ComboBox or ListBox control.|
|Value||The control’s value.|
|Left and Top||Values that determine the control’s position.|
|Width and Height||Values that determine the control’s width and height.|
|Visible||If False, the control is hidden.|
|Name||The name of the control. Default names are based on the control type. For example, CommandButton controls are named like CommandButton1, CommandButton2, and so on. But you can change the name to any valid name. However, each control’s name must be unique for a specific worksheet.|
|Picture||Enables you to specify a graphic image to display.|
Linking controls to cells
Most of the time, ActiveX controls can be used in a worksheet without using any macros. Many controls have a LinkedCell property. LinkedCell property holds the reference of a worksheet cell and this cell is linked to the control.
For example, say I have inserted two OptionButton controls on a worksheet like the following figure.
I’ve linked them to two particular cells (E6, E8). Say I choose OptionButton with the caption “Linked to Cell E6”, then cell E6 will show TRUE, and cell E8 will show FALSE. When I choose OptionButton with the caption “Linked to Cell E8”, cell E8 will show TRUE and cell E6 will show FALSE. You can set the linked cell in the properties window like the figure below.
Creating macros for controls
You know we must use Visual Basic (VB) Editor to create a macro for a control. The macros are stored in the relevant worksheet code module. To create an example, I have placed a CommandButton control on Sheet2. Then I double-clicked on this control to open the VB Editor. VB Editor opens with a code like the figure below.
There is an easy way to access the code module for a control. Just double-click the control while Excel is in Design mode.
The control’s name(in our case it is CommandButton1) appears in the upper-left portion of the code window, and the event(Click) appears in the upper-right area.
Read More:Why We Use Form Controls on a Worksheet?
I have used a step by step process to insert a CommandButton control in a worksheet and then created a simple macro that displays a message when the button is clicked:
- Choose Developer ➪ Controls ➪ Insert.
- Click the CommandButton tool in the ActiveX Controls section.
- Click anywhere in the worksheet to create the button. Excel automatically enters into Design mode.
- Right-click on the button and select Properties from the shortcut menu. In the properties window, change the caption from “CommandButton1” to “OK”.
- Double-click the button. The VB Editor window is activated, and an empty Sub procedure is created.
- Enter this VBA statement before the End Sub statement: MsgBox “Hi Friend!, it’s ” & Time. The complete code will be like this one:
Private Sub CommandButton1_Click() MsgBox "Hi Friend!, it's " & Time End Sub
- Press Alt+F11 to return to Excel.
- Click the Design Mode button in the Developer ➪ Controls section. Excel will exit design mode.
After performing the preceding steps, click the CommandButton, a message box appears with some text and the current time (your device time) like the figure below.
Happy Excelling ☕