How to Use ActiveX Controls in Excel (Step by Step)

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.

Table of Contents

Adding an ActiveX control on the worksheet
Design mode
Changing/ Adjusting properties
Common properties
- TABLE: Common Properties in Multiple Controls
Linking controls to cells
Creating macros for controls

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.

Caution: Make sure that you didn’t select the control from the Form controls. ActiveX controls are located in the lower half of the list.

Design mode

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.

Excel is in Design mode, as the Design Mode icon is highlighted.

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 properties of controls. When we create a control in a worksheet, Excel enters in Design mode automatically.

To change the properties for a control, follow the steps below:

1. At first, you have to make sure that Excel is in Design mode.
2. Click the control whose properties you want to adjust. The control will be selected.
3. Now click the Properties icon in the Controls section of the Developer tab. The Properties window appears as shown in the figure below.

How to Use the ActiveX controls on a Worksheet in Excel

Use the Properties window to change the properties of a control. Here shown an
CommandButton control properties.

Select the property that needs change/adjust 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.

Tip: To find out about a particular property, select the property in the Properties window and press F1.

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.

Common properties

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.

Note: Some ActiveX control properties are required. These properties can’t be left empty. For example, the Name property. If a property that is required is missing, Excel displays an error message to tell you about the missing value.

TABLE: Common Properties in Multiple Controls

Property	Description
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.

Why We Use Form Controls on a Worksheet?

Cell E6 and E8 are linked to two OptionButtons.

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.

Link cells in properties window when your Design Mode is activated.

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.

VB Editor opens with a code. You can create Macro for any event listed under the control.

Tip
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.

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 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:

Source code    
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.

MsgBox dialog box shows some text and the current time.

Happy Excelling ☕

Kawser

Hello!
Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share here. Not only how to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned!