Form Controls in Excel

Last Updated :

21 Aug, 2024

Blog Author :

N/A

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya

Table Of Contents

arrow

Excel Form Controls

Excel Form Controls are objects that we can insert into the worksheet to work with data and handle the data as specified. For example, using these form controls in Excel, we can create a drop-down list in excel, list boxes, spinners, checkboxes, and scroll bars.

"Form Controls" is available in Excel under the "Developer” tab excel.

Form Controls in Excel Example 1

As we can see, we have two categories, "Form Controls" and "Active X Controls." In this article, we are concentrating only on "Form Controls." The below image describes all the "Form Controls"  in Excel.

Form Controls in Excel Example 1.1

How to use Form Controls in Excel?

Now, we will see how to work with each in detail.

Form Control 1: Button

This option is to draw a button and assign any macro name to it so that the assigned macro can run when we click this button.

Form Controls in Excel Example 9

Form Control 2: Combo Box

The combo box is our drop-down list. It works the same as the drop-down list, but combo box excel is considered an object.

We must select the "ComboBox" and draw anywhere on the worksheet area.

Form Controls in Excel Example 1.2

To insert values, we must create a day list in column A.

Example 1.3

Now, select the "Combo Box," right-click and choose "Format Control."

Form Controls in Excel Example 1.4

Now in the "Format Control" window, choose "Control." Then, in the "Input range," choose the month names range of cells. Then, click on "OK."

 Example 1.5

Now, we can see the selected day list in the combo box.

Form Controls in Excel Example 1.6

Form Control 3: CheckBox

The checkbox is used to display the item selection. If checked, we can link to a certain cell to show the selection as "TRUE" and "FALSE" if unchecked.

We must first draw the checkbox on the worksheet.

Example 2

Then, right-click and choose the "Edit Text" option.

Form Controls in Excel Example 2.1

Change the default name from "Check Box1" to "Option."

 Example 2.2

Again, right-click and choose "Format Control."

Form Controls in Excel Example 2.3

Under the "Control" tab, we must choose "Unchecked" and give the "Cell link" to the D3 cell. Click "OK."

 Example 2.4

Now, check the box to see the "TRUE" value in cell D3.

Form Controls in Excel Example 2.5

And uncheck the box to see the "FALSE" value.

Example 2.6

Form Control 4: Spin Button

Using the "Spin Button," we can increment and decrement the value in the linked cell. We need to draw the spin button to see options.

Form Controls in Excel Example 3

Then, right-click on the button and choose "Format Control."

 Example 3.1

Under the "Control" tab, we can make all the settings. We can set a minimum value, maximum value, and current value. Also, we can configure what should be the incremental and decremented value when the spin button is clicked. Click "OK."

Form Controls in Excel Example 3.2

Now, if we click up the arrow of the spin button in cell A2 one, we can see the incremental value.

Example 3.3

And if we click on the down arrow of a spin button in cell A2, we can see every time it will be decreased by one.

Form Controls in Excel Example 3.4

Another thing is in the "Format Control" window, we have set 1 as the "Minimum value" and 12 as the "Maximum value."

 Example 3.5

So, when we press the up arrow, it will increment by 1 until it reaches 12. After that, it will not increase.

Form Controls in Excel Example 3.6

Form Control 5: List Box

Using the list box in excel, we can create a list of items. Let's first draw the box and then configure it.

 Example 4

For this list box, we will create a list of days.

Form Controls in Excel Example 4.1

Then, right-click on the "List Box" and choose "Format Control."

 Example 4.2

Now, under the "Control" tab for "Input range," choose the day list, and for "Cell link," choose C10 cell. Since we have selected "Single" under the "Selection type," we can select only one item at a time. Then, click "OK."

Form Controls in Excel Example 4.3.0

Now, see the list of days in the list box.

Example 4.3.4

Now select any item from the list to see what we get in linked cell C10.

Form Controls in Excel Example 4.4

As we can see above, we have 6 as the value in cell C10. From the list box, we have selected "Saturday," which is the 6th item, so the result in cell C10 is 6.

Form Control 6: Group Box

Using the group box, we can create multiple controls. We cannot interact with this; rather, it allows us to group other controls under one roof.

We must first draw the group box on the sheet.

Example 5

Then, right-click on the "Group Box" and choose "Format Control."

Form Controls in Excel Example 5.1

Insert the radio buttons that we want to group.

Example 5.2.0

Form Control 7: Label

The label does not have any interactivity with users. It will only display the value entered or cell referenced value, i.e., Welcome.

Form Controls in Excel Example 6

Form Control 8: Scroll Bar

Using the Scroll Bar in Excel, can increment and decrement the linked cell value. It is similar to "Spin Button." But in a scroll bar, we can see the scroll moving upon increasing and decreasing.

We need to draw the scroll bar first on the sheet.

Example 7

Then, right-click on the button and choose "Format Control."

Form Controls in Excel Example 8

Under the "Control" tab, we can make all the settings.

 Example 8.1

So, when we press the up arrow, it will increment by 1 until it reaches 12; then, it will not increase.

Form Controls in Excel Example 8.2.0

Things to Remember

  • This article is just an introduction to how form controls work in Excel.
  • Using these form controls in Excel, we can create interactive charts and dashboards.
  • Active X Controls are used primarily with VBA coding.

Recommended Articles

This article is a guide to Form Controls in Excel. Here, we discuss how to use form controls in Excel using the button, combo box, spin button, list box, etc., along with examples and a downloadable Excel template. You may also look at these useful functions in Excel: -