Form Controls in Excel
Excel offers various methods to ensure input values adhere to specific criteria, with Form Control being one of them. Form Controls are functional objects designed for user interaction with datasets. To access them, navigate to the Developer tab and select Insert. Here, you’ll find the Form Controls command.
Under Form Controls, several commands are available, each with its own functionality.
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 – Listing of ActiveX Control
ActiveX Controls can be utilized on worksheet forms either with or without the application of VBA code. Typically, ActiveX Controls are preferred when a more flexible design is required compared to Form Form.
ActiveX Controls offer extensive properties enabling users to customize behavior, appearance, fonts, and various other characteristics. However, it’s important to note that ActiveX Controls cannot be added to chart sheets or 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 Use Controls on a Worksheet?
UserForm controls can be directly used on a worksheet to facilitate input. For instance, when creating a model with one or more input cells, controls can allow users to set or select values for these cells.
Adding controls to a worksheet is much simpler compared to creating a dialog box using UserForm. Unlike working with UserForm, no macro creation is necessary when utilizing controls directly on a worksheet.
For example, if two OptionButton controls are inserted on a worksheet and linked to specific cells, selecting one OptionButton will update the linked cells accordingly. This linkage can be set in the properties window.
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 the properties window when your Design Mode is activated.
When controls are used directly on a worksheet, the workbook becomes highly interactive without requiring macros.
When adding controls to a worksheet through Developer ➪ Controls ➪ Insert, two different sets of controls are available: Form Controls and ActiveX Controls. For newcomers, choosing between them might be confusing.
- Form Controls: Unique to Excel, these controls can be used on UserForms or for other purposes.
- ActiveX Controls: A subset of Form Controls that can be used directly on a worksheet.
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.
ActiveX controls. ToolTip showing the description of control.
The available controls vary between Form Controls and ActiveX Controls, with some controls, such as the ListBox, appearing in both. However, they are distinct controls. In general, Form Controls are simpler to use, while ActiveX Controls enhance worksheet interactivity.
Comparison – Excel Form Control Vs ActiveX Control
Excel Form Control and ActiveX Control are similar in nature, but there are notable differences between them in terms of functionality and usage.
| 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 |
Related Articles
<< Go Back to Form Control in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
