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 creating those controls using UserForms, then this chapter is for you. Step by step, I will explain how to enhance the interactivity of your worksheet without creating custom dialog boxes.
Form Control in Excel
Excel provides several ways to ensure the input values meet specific criteria and Form Control is one of them. Actually, Form Controls are objects that are functional when the user needs to interact with the dataset. In order to get access to it, go to the Developer tab and then click the Insert option. You will see the Form Controls command.
Under the Form Controls option, there are several commands. The functionality of these commands is described below.
Form Controls Table
|Name of the Control||What It Does|
|Button||It executes the macro|
|Combo Box||It selects items from a drop-down list|
|Check Box||It controls multiple on/off options|
|List Box||It allows a user to select an item from a list|
|Scroll Bar||It increases or decreases the values of a cell to some fixed amount|
|Spin Button||It increases or decreases the values of a cell in steps to some fixed amount|
|Option Button||It has an exclusive single on/off options.|
|Label||It can either be static or linked to a cell also|
|Group Box||It allows the user to visually organize related items on a specific form|
ActiveX Control – List of ActiveX Control
A user can use the ActiveX Controls on worksheet forms with or without the application of VBA code. Usually, ActiveX Control is used when a more flexible design is needed than Form Control.
The ActiveX Control has intensive properties that allow the user to customize behavior, appearance, fonts, and many other characteristics. However, it should be kept in mind that the user is not allowed to add ActiveX Controls to chart sheets or to XLM macro sheets. The functionality of ActiveX Controls is described 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.|
Read More: How to Create Chart Slider in Excel
Why We Use Controls on a Worksheet?
The user can easily provide input values when the UserForm controls are used directly in the worksheet. 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.
Adding controls to a worksheet is much easier when compared to 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 has 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.
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 among the built-in features in Excel||1) ActiveX Controls sometimes may need to be added by the user manually|
|2) They are much simpler||2) Compared to Form Control they have a more flexible design|
|3) Form control feature is available both in Windows and Mac||3) Not available on Mac|
|4) Can not be used as an object in codes||4) Can be used as objects in VBA codes|
|5) The functionality of the Form Control cannot be extended||5) Generated from DLLs. You can extend the functionality of the ActiveX controls by using the Register Custom, which you get under More 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|
In this article, I’ve tried to gather some ideas about Form Control and ActiveX Control and the key differences (Form Control vs ActiveX Control) between them. Hope this article has shed some light on your journey of learning. If you have any queries, don’t forget to share them in the comment box below. Thanks for keeping in touch.
Happy Excelling ☕