Excel ActiveX Controls Properties (Checkbox, Combo box, List box, …)

Last updated on May 5th, 2018

In this article, I am going to give an overview of available ActiveX controls that you can use in your worksheet. After reading this article, you will know how to use them and their important properties.

I will cover here most of the ActiveX controls available in Excel VBA: CheckBox, Combo Box, CommandButton, Image, Label, ListBox, OptionButton, ScrollBar, SpinButton, TextBox, ToggleButton

This article is part of my series: Excel VBA & Macros – A Step by Step Complete Guide.

ActiveX control CheckBox and its properties

A CheckBox control is useful when you need to know whether the choice is yes or no, TRUE or FALSE, on or off, and so on.

Learn Excel VBA Online: Top Excel VBA Courses Online

The following two are the most useful properties of CheckBox control:

  • Accelerator: Accelerator is actually a shortcut key. Lets clear it with an example. Say I have inserted a CheckBox control in the worksheet, then changed the Caption properties of this control to “Europe”. In the Properties window, I have inserted letter “E” as the Accelerator value. “E” letter of the CheckBox Caption will be underlined.
  • LinkedCell: The worksheet cell that is linked to the CheckBox. The linked cell displays TRUE if the control is checked or FALSE if the control is not checked.

See the figure below. I have placed three CheckBoxes. Caption, Accelerator, and LinkedCell properties have been changed in the Properties window.

If you don’t know how to use ActiveX controls, then check out this article at first: How to use ActiveX controls in Excel.
Overview of ActiveX controls on a worksheet in Excel

CheckBox with underlined Accelerator keys.

ActiveX control ComboBox and its properties

A ComboBox control is almost similar to a ListBox control. But there are differences between them. A ComboBox is a drop-down list. ComboBox displays only one item at a time. ListBox is not a drop-down list; it displays all the items in the list at a time. Another difference is that, with a ComboBox, the user can enter a value that is not in the list of items.

The following are the most useful properties of ComboBox control:

  • BoundColumn: Sometimes, your ListFillRange may contain multiple columns. You can use BoundColumn property to specify which column Excel will display in the linked cell of ComboBox control. For example, assume that you have entered “D1: E12” in the ListFillRange property and then you have entered 2 in the BoundColumn property. In result, ComboBox will display the values from Column 2 in the ListFillRange (D1: E12). See the figure below.
    Overview of ActiveX controls on a worksheet in Excel

    BoundColumn property explained in the above figure.

  • ColumnCount: The number of columns in the list. If ColumnCount property value is set 2 in the above example, it will show both column in the ComboBox list.
    Overview of ActiveX controls on a worksheet in Excel

    Use ColumnCount Property to show more than one column in the ComboBox list.

  • LinkedCell: The worksheet cell that is linked to the ComboBox. The linked cell displays the selected value from the ComboBox control.
  • ListFillRange: This property contains a worksheet range (like “D1:E12” in the above example). The worksheet range can be single or multiple column.
  • ListRows: This property by default holds 8. You can change it. This number of items is displayed when the list drops down.
  • ListStyle: You can determine how the list will look like using this property. There are two options: 0-fmListStylePlain and 1-fmListStyleOption.
  • Style: Using this property, you can determine whether the control will act like a drop-down list or a ComboBox. If you choose drop-down list, you can’t enter new value in the control.

See the figure below; a ComboBox control is shown.

Overview of ActiveX controls on a worksheet in Excel

A ComboBox control.

ActiveX control CommandButton

A CommandButton control is used to execute a macro. When a CommandButton is clicked, it executes an event procedure. An event procedure name has three parts: at first the CommandButton name, then an underscore, and at last the word Click.

For example, say your CommandButton name is MyButton. You want to execute a macro when MyButton is clicked. So the procedure name will like this: MyButton_Click. This macro is stored in the relevant worksheet code module.

Private Sub CommandButton1_Click()
MsgBox "Hello " & Application.UserName & "." & vbCrLf & "It's " & Time
End Sub

 

The above macro will be activated when I click the CommandButton shown in the following figure.

Overview of ActiveX controls on a worksheet in Excel

Click on ‘Click Me!’ to see your name showed in a dialog box.

ActiveX control Image and its properties

An Image control displays an image in the worksheet.

These are the most useful properties of an Image control:

  • AutoSize: If this property is TRUE, the Image control will be resized automatically to fit the image.
  • Picture: Picture property holds the path to the image. Click Picture button in the Properties window, Excel will display a dialog box named “Load Picture”. Find out the image you want to insert in the worksheet and upload it to this dialog box.
Overview of ActiveX controls on a worksheet in Excel

An Image Control with an image. AutoSize is TRUE.

Tip: There is another way to insert an image on a worksheet. Use Insert ➪ Illustrations ➪ Picture command to do it. In fact, there is no advantages of using Image control to insert an image on a worksheet.

ActiveX control Label

A Label control simply displays text. This control isn’t very useful to display text on a worksheet. A TextBox control can give you more flexibility.

Overview of ActiveX controls on a worksheet in Excel

A Label Control on a worksheet.

ActiveX control ListBox and its properties

A ListBox control displays a list of items. The user can select one or more items from the list. It’s similar to a ComboBox. The differences are:

  1. ListBox displays more than one item at a time, ComboBox displays only one item at a time.
  2. You can select more than one item from the ListBox, but you can select only one item using ComboBox.
Overview of ActiveX controls on a worksheet in Excel

ListBox control with different properties.

The most important properties of ListBox are described below:

  • BoundColumn: Sometimes, your ListFillRange property may contain multiple columns. You can use BoundColumn property to specify which column Excel will display in the linked cell as return value.
  • ColumnCount: The number of columns in the list. If ColumnCount property value has been set 2 in the above figure, in result it shows both columns in the ListBox control.
  • IntegralHeight: If it is TRUE, the height of the ListBox control adjusts automatically to display full list. If it is FALSE, the ListBox may display partial list.
  • LinkedCell: The worksheet cell that is linked to the ListBox. The linked cell displays the selected value from the ListBox control.
  • ListFillRange: This property contains a worksheet range. An example of a worksheet range is: “G4: H15”. The worksheet range can be a single or multiple columns.
  • ListStyle: You can determine how the list will look like using this property. There are two options: 0-fmListStylePlain and 1-fmListStyleOption. In above figure, I have used the second value: 1-fmListStyleOption
  • MultiSelect: You can determine whether the selection from the ListBox will be single or multiple. There are three options on this list.
Note: If you select the MultiSelect option for your ListBox, you can’t use the LinkedCell property.

ActiveX control OptionButton and its properties

OptionButton controls enable the user to select from a small number of items. OptionButtons are always used in groups. At least two OptionButtons will make a group.

Overview of ActiveX controls on a worksheet in Excel

If you select Blue, cell F8 will be Blue and so on.

The most important properties of OptionButton are described below:

  • Accelerator: Accelerator is actually a shortcut key. Lets clear it with an example. Say I have inserted an OptionButton control in the worksheet, then changed the Caption properties of this control to “Red”. In the Properties window, I have inserted letter “R” as the Accelerator value. In result, pressing Alt+R will select the control. “R” letter of the OptionButton Caption will be underlined.
  • GroupName: Two or more OptionButton make a group. A group has its unique name. For example, say you have four OptionButtons in two groups: Group1 (OptionButton1 and OptionButton2) and Group2 (OptionButton3 and OptionButton4). So, in Properties window you will enter “Group1” into GroupName property for OptionButton1 and OptionButton2. Same way OptionButton3 and OptionButton4 will share same GroupName “Group2”.
  • LinkedCell: The worksheet cell that’s linked to the OptionButton. The cell displays TRUE if the control is selected or FALSE if the control isn’t selected.

ActiveX control ScrollBar and its properties

A ScrollBar control is useful for specifying a cell value from a wide range of data.

Overview of ActiveX controls on a worksheet in Excel

A ScrollBar Control.

The most important properties of OptionButton are described below:

  • Value: The current value of the control.
  • Min: The minimum value for the control.
  • Max: The maximum value for the control.
  • LinkedCell: The worksheet cell that’s linked to the OptionButton. The cell displays TRUE if the control is selected or FALSE if the control isn’t selected.
  • SmallChange: The amount that the control’s value is changed by a click.
  • LargeChange: The amount that the control’s value is changed by clicking either side of the button.

Read More: Why We Use Form Controls on a Worksheet?

ActiveX control SpinButton and its properties

With a SpinButton control, a user can select a value by clicking the control. This control has two arrows: one to increase the value and the other to decrease the value. You can place this control in your worksheet either horizontally or vertically.

Overview of ActiveX controls on a worksheet in Excel

SpinButton placed horizontally in the worksheet.

The most important properties of SpinButton control are described below:

  • Value: The current value of the control.
  • Min: The minimum value of the control.
  • Max: The maximum value of the control.
  • LinkedCell: The worksheet cell that is linked to this control. The linked cell displays the returned value from the SpinButton control.
  • SmallChange: The amount changed by a single click. By default, this property is set to 1, but you can change it for any value.

ActiveX control TextBox and its properties

A TextBox control is more useful as an output control than as an input control. You can use a general worksheet cell to take text data. Because a TextBox control provides scroll bars, you can display a big chunk of text data in a small area using this control.

The figure below shows a TextBox control containing a large size text data. This data is entered into the Text property of TextBox control.

Overview of ActiveX controls on a worksheet in Excel

A TextBox control displaying data.

The most important properties of SpinButton control are described below:

  • AutoSize: If this property is TRUE, the TextBox control will be resized automatically to fit the text.
  • IntegralHeight: If it is TRUE, the height of the TextBox control adjusts automatically to display the full text. If it is FALSE, the TextBox may display partial text.
  • MaxLength: The maximum number of characters allowed in the TextBox.
  • MultiLine: If TRUE, the TextBox can display more than one line of text.
  • TextAlign: Determines the text orientation in the TextBox. It has three options.
  • WordWrap: Determines whether the control allows word wrap.
  • ScrollBars: Determines the type of ScrollBars for the control: horizontal, vertical, both, or none.

ActiveX control ToggleButton

A ToggleButton control has two modes: on and off. ToggleButton changes its mode and appearance from on to off or off to on when it is clicked. Its value is either TRUE(pressed) or FALSE(not pressed). ToggleButton can be used in place of CheckBox control.

Overview of ActiveX controls on a worksheet in Excel

A ToggleButton Control.

Wrapping Up

So this is all from me on this topic. Don’t forget to download the sample file and let me know you face any difficulties using this article and the file.

Happy Excelling ☕

Download Working File

Download the working file from the link below:

ActiveX-Controls1.xlsm


Kawser on EmailKawser on FacebookKawser on LinkedinKawser on TwitterKawser on Youtube
Hello!

Welcome to my Excel blog! I am conducting deep dives into the world of Excel. Please join with me and explore Excel deeply.

Keep in mind this African proverb:

"If you want to go fast, go alone,
If you want to go far, go together."

Let's together explore Excel deeply!

3 Comments
  1. […] Excel ActiveX Controls – Learn How to Use 11 ActiveX with Examples. […]

  2. Reply stefan.mueller.83@gmail.com'
    Stefan March 22, 2016 at 7:50 PM

    Hallo Kawser,
    have ever tried to use ActiveX Controls within VSTO, I have troubles addressing their event handling procedures.
    stefan

  3. Reply krishnaprasad.menon@sunpharma.com'
    Krishnaprasad Menon November 7, 2016 at 10:56 AM

    Excellent tutorial. Thanks

    Leave a reply