Form Controls in Excel

Excel Form Controls

Excel Form Controls are objects which can be inserted at any place in the worksheet to work with data and handle the data as specified. Using these form controls in excel we can create a drop-down list in excel, list boxes, spinners, checkboxes, scroll bars.

In excel, “Form Control” is available under the “Developer” tab excel.

Form Controls in Excel Example 1

As you can see, we have two categories here, “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 ” in detail.

Form Controls in Excel Example 1.1

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Form Controls in Excel (wallstreetmojo.com)

How to use Form Controls in Excel?

Now we will see how to work with each one of them in detail.

You can download this Form Controls Excel Template here – Form Controls Excel Template

Form Control 1: Button

This is simply to draw a button and assign any macro name to it so that the assigned macro will run when you click this button.

Form Controls in Excel Example 9

Form Control 2: Combo Box

Combo Box is simply our drop-down list, and it works exactly the same as the drop-down list, but the combo box excelCombo Box ExcelCombo Box in Excel is a type of data validation tool that can create a dropdown list for the user to select from the pre-determined list. It is a form control which is available in the insert tab of the developer’s tab.read more is considered as an object.

Select the “Combo Box” and draw anywhere on the worksheet area.

Form Controls in Excel Example 1.2

Now to insert values, 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.” In the “Input Range,” choose month names range of cells. Click Ok.

 Example 1.5

Now we should 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 it is checked, we can link to a certain cell to display the selection as TRUE and FALSE if it is not checked.

Draw the checkbox on the worksheet.

Example 2

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, choose “Unchecked” and give cell link to D3 cell. Click Ok

 Example 2.4

Check the box to see the TRUE value in cell D3.

Form Controls in Excel Example 2.5

Now 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. Draw the spin button to see options.

Form Controls in Excel Example 3

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; we can set a maximum value, we can set current value, and 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 you click up the arrow of the spin button in cell A2 one, we can see the incremental value.

Example 3.3

And if you 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 the minimum value is 1, and the maximum value is 12.

 Example 3.5

So when you press 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

By using the list box in excelList Box In ExcelThe list box in Excel VBA is a list assigned to a variable with a variety of inputs to select from. It allows multiple options to be selected at the same time and can be added on a UserForm using the list box option.read more, 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

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 chooses C10 cell. Since I have selected “Single” under the Selection type, we can select only one item at a time. 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 you can see above, we have got 6 as the value in cell C10; this is because, 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

By using Group Box, we can create multiple controls in this. We cannot have interaction with this; rather, it allows us to group other controls under one roof.

Draw the group box on the sheet.

Example 5

Right-click on the “Group Box” and choose “Format Control.”

Form Controls in Excel Example 5.1

Insert the radio buttonsInsert The Radio ButtonsIn Excel, radio buttons or options buttons record a user's input. They can be found in the developer's tab's insert section. read more that you want to group.

Example 5.2.0

Form Control 7: Label

The label doesn’t have any interactivity with users. This will only display value entered or cell referencedCell ReferencedCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1.read more value, i.e., Welcome.

Form Controls in Excel Example 6

Form Control 8: Scroll Bar

By using Scroll Bar in Excel, we can increment and decrement the linked cell value. This is similar to Spin Button, but in a scroll bar, we can see the scroll moving upon increasing and decreasing.

Draw the Scroll bar on the sheet.

Example 7

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 you press up arrow, it will increment by 1 until it reaches 12; after that, it will not increase.

Form Controls in Excel Example 8.2.0

Things to Remember

Recommended Articles

This has been a guide to form controls in excel. Here we discuss how to use form controls in excel using Button, Combo Box, Spin Buton, List Box, etc., along with examples and a downloadable excel template. You may also look at these useful functions in excel –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>