In our previous articles, you have been introduced to Excel UserForms. In this chapter, I am going to discuss Excel Form Control Vs Activex Control.
If you like to use the controls used in a custom dialog box but dislike to create those controls using UserForms, then this chapter is for you. In step by step, I’m going to explain how to enhance the interactivity of your worksheet without creating custom dialog boxes.
This article is part of my series: Excel VBA & Macros – A Step by Step Complete Guide.
Excel Form Control Vs Activex Control
Excel Form Control and Activex Control are kinds of the same. The applications are quite the same but still, there are some differences between them.
|Form Control||ActiveX Control|
|1) Form Controls are the built-in feature in Excel||1) ActiveX Controls sometimes need to be added manually by the user|
|2) They are much simpler||2) They have a more flexible design than Form Control|
|3) Form control feature is available both in Windows and Mac||3) Not available in Mac|
|4) Can not be used as an object in codes||4) Can be used as objects in codes|
|5) The functionality of the Form Control cannot be extended||5) These are generated from DLLs. By using the Register Custom under More Controls, you can extend the functionality of the ActiveX controls.|
|6) Form Controls don’t have any properties settings||6) ActiveX Control has properties settings|
|7) Excel response to the Form control, after every updating or editing on it||7) The response to the ActiveX control in Excel is continuous|
Why We Use Controls on a Worksheet?
When the UserForm controls are used directly in the worksheet, the user can easily provide input values. For example, if you create a model that uses one or more input cells, you can use controls to allow the user to set or select values for the input cells.
Read More: How to Use Form Controls in Excel
Adding controls to a worksheet is much easy when compared with creating a dialog box using UserForm. In this blog post, we had to create a macro to show the dialog box when we worked with UserForm. You don’t need to create any macro to work with controls created directly on a worksheet.
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.
When you use controls on a worksheet directly, the workbook becomes very interactive, but it uses no macros.
When you choose Developer ➪ Controls ➪ Insert to add controls to a worksheet, you will get two different sets of controls: Form controls and ActiveX controls. You may be confused about which one to use if you’re new.
- Form controls: These controls are unique to Excel, you can use them on UserForms or any other purpose.
- ActiveX controls: These controls are a subset of Form controls that can be used on a worksheet directly.
The figure below shows the controls that display when you choose Developer ➪ Controls ➪ Insert. Move your mouse pointer over a control, Excel will display a ToolTip that describes the control.
You may be more confused as many controls are available in both sources. For example, a control named ListBox is listed in Form controls, and ActiveX controls both. But remember, they’re two totally different controls. In general, Form controls are easier to use, but ActiveX controls add more interactivity to a worksheet.
A description of ActiveX controls appears in the Table below:
ActiveX controls Table
|Name of the Control||What It Does|
|Command Button||Inserts a CommandButton control. It creates a clickable button.|
|Combo Box||Inserts a ComboBox control. It creates a drop-down list.|
|Check Box||Inserts a CheckBox control. It controls Boolean options.|
|List Box||Inserts a ListBox control. It allows a user to select an item from a list.|
|TextBox||Inserts a TextBox control. It allows a user to type text.|
|Scroll Bar||Inserts a ScrollBar control. It is used to input a value by dragging a bar.|
|Spin Button||Inserts a SpinButton control. It is used to input a value by clicking up or down.|
|Option Button||Inserts an OptionButton control. It allows a user to select from multiple options.|
|Label||Inserts a Label control. It just contains some information about something.|
|Image||Inserts an Image control. It holds an image.|
|Toggle Button||Inserts a ToggleButton control. It controls Boolean options.|
|More Controls||Displays a list of other ActiveX controls that are installed on your system. All of these controls may not work with Excel.|
Happy Excelling ☕