ActiveX Controls Excel [Check box, Combo box, List box, Text box & More]



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.
This article is part of my series: Excel VBA & Macros – A Step by Step Complete Guide.

Download Working File

To follow along me with this article, at first, download the working file from the link below:

02.Download

ActiveX control: CheckBox

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.

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.

Overview of ActiveX controls on a worksheet in Excel

CheckBox with underlined Accelerator keys.

ActiveX control: ComboBox

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 Click Me! to see your name showed in a dialog box.

ActiveX control: Image

An Image control display 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 with this dialog box.
Overview of ActiveX controls on a worksheet in Excel

An Image Control with an image. AutoSize is TRUE.



Tip
There is an other 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

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 column 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 single or multiple column.
  • 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 in this list.

Note
If you select MultiSelect option for your ListBox, you can’t use the LinkedCell property.

ActiveX control: OptionButton

OptionButton controls enable 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

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.

ActiveX control: SpinButton

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

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 provide 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 ☕

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!

You may also like...

3 Responses

  1. stefan.mueller.83@gmail.com' Stefan says:

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

  2. krishnaprasad.menon@sunpharma.com' Krishnaprasad Menon says:

    Excellent tutorial. Thanks

  1. August 26, 2015

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

Leave a Reply

Your email address will not be published. Required fields are marked *

Solve the Math * Time limit is exhausted. Please reload CAPTCHA.