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, list boxes, spinners, checkboxes, scroll bars.
In excel “Form Control” is available under the “Developer” tab.
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.
How to use Form Controls in Excel?
Now we will see how to work with each one of them in detail.
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 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 is considered as an object.
Select the “Combo Box” and draw anywhere on the worksheet area.
Now to insert values create day list in column A.
Now select the “Combo Box” right click and choose “Format Control”.
Now in the “Format Control” window choose “Control”. In the “Input Range” choose month names range of cells. Click Ok.
Now we should see the selected day list in the combo box.
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.
Right-click and choose “Edit Text” option.
Change the default name from “Check Box1” to “Option”.
Again right-click and choose “Format Control”.
Under the “Control” tab choose “Unchecked” and give cell link to D3 cell. Click Ok
Check the box to see the TRUE value in cell D3.
Now uncheck the box to see the FALSE value.
Form Control 4: Spin Button
Using “Spin Button” we can increment and decrement the value in the linked cell. Draw the spin button to see options.
Right-click on the button and choose “Format Control”.
Under the “Control” tab we can make all the settings. We can set 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.
Now if you click up the arrow of the spin button in cell A2 one we can see the incremental value.
And if you click on down arrow of a spin button in cell A2 we can see every time it will be decreased by one.
Another thing is in the format control window we have set the minimum value is 1 and the maximum value is 12.
So when you press up arrow it will increment by 1 until it reaches 12 after that it will not increase.
Form Control 5: List Box
By using list box we can create a list of item, let’s first draw the box and then configure it.
For this list box, we will create a list of days.
Right-click on the “List Box” and choose “Format Control”.
Now under the “Control” tab for “Input Range” choose 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.
Now see list of days in the list box.
Now select any item from the list to see what we get in linked cell C10.
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 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.
Right-click on the “Group Box” and choose “Format Control”.
Insert the radio buttons that you want to group.
Form Control 7: Label
The label doesn’t have any interactivity with users. This will only display value entered or cell referenced value i.e Welcome.
Form Control 8: Scroll Bar
By using Scroll Bar 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.
Right-click on the button and choose “Format Control”.
Under the “Control” tab we can make all the settings.
So when you press up arrow it will increment by 1 until it reaches 12 after that it will not increase.
Things to Remember
- This is just the introduction of how form controls works in excel.
- Using these form controls in excel we can create interactive charts and dashboards.
- Active X Controls are mainly used with VBA coding.
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 downloadable excel template. You may also look at these useful functions in excel –
- VBA Combo Box
- Excel Interactive Chart
- Most Powerful Hacks in Excel
- Bullet Chart | Excel Examples
- How to Insert Button in Excel?
- Control Excel Charts
- Excel Checklist
- Name Box Excel
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
