Key Differences in Excel: Form Control Vs. ActiveX Control

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.

This article is part of my series: Excel VBA & Macros – A Step by Step Complete Guide.


Form Control in Excel

Excel provides several ways for ensuring 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.

Form Control in Excel

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

Read More: How to Use Form Controls in Excel


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 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 Use ActiveX Controls 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 easy 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.

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

Why We Use Form Controls on a Worksheet?

ActiveX controls. ToolTip showing the description of 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.

Read more: How to create VBA Macros in Excel using Macro Recorder


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

Conculsion

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. You can also visit our official website ExcelDemy to get more related articles. Thanks for keeping in touch.

Happy Excelling ☕


Further Readings

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo