Excel Form Control: A Complete Guide

In this article, you will learn how to insert and use Excel Form Control. This article will cover everything from turning on the Developer tab to inserting specific Form Control and linking them to your dataset.

Form Controls provide a simple and intuitive interface for users to interact with data and perform actions. Form Controls, such as Combo Boxes and Option Buttons, can streamline data entry by presenting predefined choices to users. If you want to showcase your skill as an advanced Excel user and present your data with more professionalism, this article will be your ultimate guideline.

Download Practice Workbook

Download this file to practice with the article.

Excel Form Control.xlsm

Introduction to Form Controls in Excel

Excel Form Controls are interactive elements or objects that can be added to an Excel worksheet to facilitate user input, data manipulation, and interactivity. They provide a way for users to interact with data and perform actions without directly manipulating the underlying cells or formulas.

Steps to Insert a Form Control in Excel

1. Turn On Developer Tab

Go to File tab in the ribbon. Click on it and Excel will show its Backstage view.

Here, at the left-down corner, you will see Options and click on it.

After you click Options, a box containing several options will appear.
Select Customize Ribbon.
At Main Tabs, you will have to check the box of the Developer option.
Finally, press OK.

Now, you will be able to see Developer tab.

2. Choose Desired Form Control

First, go to Developer tab.
Select the Insert option from Controls group.

Finally, from the drop-down list, you can select any control button from the Form Controls section.

How to Use Form Controls in Excel

1. Form Control: Button

First, go to Developer tab.
Select the Insert option from Controls group.
From the drop-down, select the Button command from the Form Controls option.

You will see a changed cursor and it will be like a plus (+) sign.
Drag the plus (+) sign and create a button.
Set a name for the button.
Right-click on the button and select Edit Text from the Context Menu.

Again, right-click on the button and select Assign Micro.

Select New in the Assign Macro dialogue box.

As a result, a VBA module window will appear on the screen.
Then, write the following code on the module and then close the module.

Sub Button1_Click()
UserForm1.Show
End Sub

Note:
We’ve already created a Userform beforehand and the code will activate when the button is clicked.

Now, click the button and you will see the UserForm appear.

2. Form Control: Combo Box

We’ll create a combo box of 12 months. When a month is selected the profit will be shown for that month in a cell.

First, go to Developer tab.
Select the Insert option from Controls group.
From, the drop-down select the Combo Box from the Form Controls option.

You will see a changed cursor and it will be like a plus (+) sign.
Drag the plus (+) sign and create a box.
To enlist the months, right-click on the box and select Format Control.

In the Control tab of the Format Control dialogue box, put $C$5:$C$16 in Input range.
When you select a month, the serial number of that month will be printed on a cell. Insert $B$21 on Cell link box.
Finally, provide 12 to Drop down lines box and press OK.

You can see the months are enlisted in the combo box.

Select March and the serial number will print on cell B21.

To get the month you selected in the combo box in cell C21, use this formula based on the VLOOKUP function and press Enter.

=VLOOKUP($B$21,$B$5:$F$16,2,TRUE)

To get the Profit/Loss for the respective month you selected in the combo box in cell D21, use this formula based on the VLOOKUP function and press Enter.

=VLOOKUP($B$21,$B$5:$F$16,5,TRUE)

Now choose the month of August from the combo box, the related data will be shown in the second table.

To hide column B for better presentation, right-click on the column header.
Then from the Context Menu, select Hide.

There you go. You have a dynamic dataset with a functioning combo box.

3. Form Control: Check Box

We’ll create 2 Check Boxes to highlight months that have generated profit and months that have incurred loss.

First, select Check Box from Form Controls.

You will see a changed cursor and it will be like a plus (+) sign.
Drag the plus (+) sign and create a box.
Set a name for the box.
Right-click on the box and select Edit Text from the Context Menu.

Again to link a cell for the check box, right-click on the box and select Format Control.

When you check/uncheck the Check Box, a cell will show TRUE/FALSE. To link that cell, insert $H$6 on Cell link box in the Control tab of the Format Control dialogue box.
Press OK to close the window.

Now if you check the box, you will notice cell H6 will show TRUE.

To link this Check Box with our table, go to Home tab.
From Styles group, choose Conditional Formatting drop-down menu.
Select New Rule.

In the New Formatting Rule window, pick Use a formula to determine which cells to format from the Select a Rule Type bar.
Then, enter the following formula in the Edit the Rule Description box:

=AND($H$6=TRUE, $E5>0)

Subsequently, to choose a Fill color, click Format.

Now select the Fill tab and the color you want.

Pressing OK will take you back to the New Formatting Rule.
Next, Press OK to apply the change.

Now if you check the box, you will see the months that have generated profit have been highlighted.

Create another Check Box following the previous steps and link a cell.
Put another Conditional Formatting and use this formula.

=AND($H$8=TRUE, $E5<0)

Select a formatting color.

If you’ve done everything you will have 2 functioning checkboxes.

To hide linked cells for better presentation, right-click on the column header, and from the Context Menu, select Hide.

There you go. You have a dynamic dataset with 2 functioning checkboxes.

4. Form Control: Spin Button

We’ll create an up and down Spin Button that will rotate 12 months. When a month is selected the profit will be shown for that month in a cell.

First, select Spin Button from Form Controls.

You will see a changed cursor and it will be like a plus (+) sign.
Drag the plus (+) sign and create a box.
To rotate the months, right-click on the box and select Format Control.

In the Control tab of the Format Control dialogue box, put 1 in Minimum value box.
Insert 12 as Maximum value.
When you select a month, the serial number of that month will be printed on a cell. Insert $B$19 on Cell link box and press OK.

When you press the up/down button, numbers 1 to 12 will rotate on cell B19.

To get the month rotating as per button click in cell C19, use this formula based on VLOOKUP function and press Enter.

=VLOOKUP($B$19,$B$5:$F$16,2,TRUE)

To get the Profit/Loss for the respective month in cell D19, use this formula based on the VLOOKUP function and press Enter.

=VLOOKUP($B$19,$B$5:$F$16,5,TRUE)

Now, if click the down button, Month and respective Profit/Loss will rotate.

To hide column B for better presentation, right-click on the column header, and from the Context Menu, select Hide.

There you go. You have a dynamic dataset with a functioning Spin button.

5. Form Control: List Box

We’ll create a list box of 12 months. When a month is selected, that month’s row in the table will be highlighted.

First, select List Box from Form Controls.

You will see a changed cursor and it will be like a plus (+) sign.
Drag the plus (+) sign and create a box.
To enlist the months, right-click on the box and select Format Control.

In the Control tab of the Format Control dialogue box, put $C$5:$C$16 in Input range.
When you select a month, the serial number of that month will be printed on a cell. Insert $J$6 on Cell link box.
Select Single in Selection type and press OK.

As you can see, the months are enlisted in the combo box.
When you select August, the serial number will print on cell J6.

To link this List Box with our table, go to Home tab.
From Styles group, choose Conditional Formatting drop-down menu.
Select New Rule.

In the New Formatting Rule window, pick Use a formula to determine which cells to format from the Select a Rule Type bar.
Then, enter the following formula in the Edit the Rule Description box:

=$J$6=$B5

Subsequently, to choose a Fill color, click Format.

Hide the helper columns B and J.

Now, if you select a month from the list box, you will see that particular is highlighted. Here, we selected March in the list box, and the corresponding row is highlighted.

6. Form Control: Option Button

We’ll create 12 Option Buttons for 12 months. When a button for a particular month is selected, that month’s row in the table will be highlighted.

First, select Option Button from Form Controls.

You will see a changed cursor and it will be like a plus (+) sign.
Drag the plus (+) sign and create a box.
Right-click on the box and select Edit Text from the Context Menu.
Set a name for the box.

Again select the option button, right-click on the button, and select Format Control.

When you check/uncheck the Option Button, a cell will show the Button To link that cell, insert $G$5 on Cell link box in the Control tab of the Format Control dialogue box.
Press OK to close the window.

Copy the button and paste them in next rows.
You will have 12 buttons like this for each month.
When you click a button, cell G5 will show its serial number.

To link these Option Buttons with our table, go to Home tab.
From Styles group, choose Conditional Formatting drop-down menu.
Select New Rule.

In the New Formatting Rule window, pick Use a formula to determine which cells to format from the Select a Rule Type bar.
Then, enter the following formula in the Edit the Rule Description box:

=$G$5=$B5

Subsequently, to choose a Fill color, click Format.

Hide the helper columns B and G.

Now, if you select a month, you will see that particular row is highlighted. Here, we selected March, and the corresponding row is highlighted.

7. Form Control: Group Box

A Group Box serves as a visual grouping element for related controls. It provides a way to organize and categorize controls that are logically connected or belong to the same group.

In the previous method of Check Box, we created 2 interactive checkboxes. We will store them in a Group Box.

First, select Group Box from Form Controls.

You will see a changed cursor and it will be like a plus (+) sign.
Drag the plus (+) sign and create a box over the Check Boxes.
Right-click on the box and select Edit Text from the Context Menu.
Set a name for the box.

To group the controls, select each one of them by pressing Ctrl and right-click on them.
Select Group from Context Menu and finally select Group option.

Note:
Users can not interact with Group Boxes. They only house other controls.

8. Form Control: Label

Labels don’t interact with users. They only showcase a certain value or text.

9. Form Control: Scroll Bar

We’ll create a Scroll Bar that will rotate 12 months. When a month is selected the profit will be shown for that month in a cell.

First, select Scroll Bar from Form Controls.

You will see a changed cursor and it will be like a plus (+) sign.
Drag the plus (+) sign and create a box.
To rotate the months, right-click on the box and select Format Control.

In the Control tab of the Format Control dialogue box, put 1 in Minimum value box.
Insert 12 as Maximum value.
When you select a month, the serial number of that month will be printed on a cell. Insert $B$19 on Cell link box and press OK.

When you press the Scroll Bar, numbers 1 to 12 will rotate on cell B19.

To get the month rotating as per the button click in cell C19, use this formula based on VLOOKUP function and press Enter.

=VLOOKUP($B$19,$B$5:$F$16,2,TRUE)

To get the Profit/Loss for the respective month in cell D19, use this formula based on the VLOOKUP function and press Enter.

=VLOOKUP($B$19,$B$5:$F$16,5,TRUE)

Hide the helper column B and you will get this.

Pros of Form Controls

Excel form control provides a simple and intuitive interface for users to interact with data and perform actions.
You can enforce Data Validation rules via Form Controls, ensuring that users submit data within predefined ranges or formats.
You can customize Excel Form Controls to match the design and aesthetics of your workbook.
Excel form controls are compatible with various versions of Excel, making your workbooks accessible to a wide range of users.

Frequently Asked Questions

1. What is the difference between Form Controls and ActiveX Controls in Excel?
Ans: Form Controls are basic controls in Excel that provide simple user interface elements such as Buttons, Checkboxes, and Drop-down lists. ActiveX Controls, on the other hand, offer more advanced features and customization options. ActiveX Controls require programming skills, while you can add and configure Form Controls directly through the Excel interface.

2. Can I assign macros or VBA code to Form Controls in Excel?
Ans: Yes, you can assign macros or VBA code to Form Controls in Excel. Right-click on the Form Control, select Assign Macro and choose the desired macro or VBA code to associate with the control. This enables you to execute specific actions or automate tasks when the user interacts with Excel Form Control.

3. How do I protect Form Controls from being modified or deleted in Excel?
Ans: To protect Form Controls from being modified or deleted in Excel, you can protect the worksheet or workbook.

Right-click on the sheet tab, select Protect Sheet or Protect Workbook and set a password if desired.
Ensure that the Edit Objects or Delete Objects option is unchecked to prevent changes to the Form Controls.

Conclusion

Excel Form Control is a powerful toolset that improves user experience and efficiency when working with data. With their user-friendly interface, data validation capabilities, and customizable appearance, they make data entry easier and more accurate. They empower users to maximize Excel’s potential, making data management and analysis accessible and effective.

Excel form Control: Knowledge Hub

How to Use Form Controls
How to Remove a Form Control
How to Create Chart Slider
How to Make Games
Excel Checkbox
Key Differences in Excel: Form Control Vs. ActiveX Control

<< Go Back to Learn Excel

Get FREE Advanced Excel Exercises with Solutions!