Distinguishing Between Excel Form Controls and ActiveX Controls

 

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.

Form Control in Excel

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.

Why We Use Form Controls on a Worksheet?

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.

Why We Use Form Controls on a Worksheet?

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 DeveloperControlsInsert, 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.

Why We Use Form Controls on a Worksheet?

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!
Kawser Ahmed
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo