Excel Form Controls (Table of Contents)
Introduction to Form Controls in Excel
Form Controls objects are used for creating buttons, boxes, list bar, and scrolls. Form controls can be accessed from Developer menu tab with two types of sections in it. First is Form Controls and the other is ActiveX Controls. Here we will only be seeing Form Controls in detail. In Form Controls, we have Buttons, Combo box, CheckBox, Spin Button, List Box, Option Box, Group Box, Scroll Bar, Text Field, Combo List-Edit, Combo Drop Down-Edit respectively. Below we have the screenshot.
How to Use Form Control in Excel?
Now to use Form Control, Go to Developer menu tab (If activated from File menu option). There we can find Form Control under Control section as shown in the below screenshot.
Form Controls in Excel allow us to create different types of controls by just selecting and dragging it anywhere in the worksheet. The work doesn’t end here, we will need to connect the inserted Form Controls with the data where just by scrolling, checking the boxes we would get some values output.
Example #1
We will see all the objects in Form Control one by one in this example. For this, just simply need to select the Form Control boxes from the menu option and drag and draw to get the button shape object.
1. Buttons – Once we select and draw this button, we will immediately get a dialog box of Assign Macro. Now there, edit and give a name to Macro Name. Here we have kept it as Button.
We will then get a button in the area where it is drawn.
We can edit the name of button as well, just by putting the cursor on it.
To assign a macro to this button, right-click and select Assign Macro.
2. Combo box – To use Combo Box, select the option and draw anywhere in the sheet.
To assign value to the Combo Box drop-down, click right on it and select Format Control.
Select the range of input cells. Here it is from A1:A5 with drop down lines as 5.
Once done, click on the drop-down arrow, we will be able to see all the selected values. And we can select any value as well.
3. CheckBox – This is used when we just need to mark an option for status. Select this option from Form Control and draw it.
Just by a single click on it, we will see a Tick inside the box.
Right-click on the box and select Format Controls. Select type and cell link as shown below.
When we check the CheckBox with a tick mark, we will get TRUE at the selected cell. And FALSE when uncheck.
4. Spin Button – As the name says, by this we can spinning around the selected values. Select and draw this on the worksheet.
Right-click and select Format Control.
Now under the Control tab, give the limit as per requirement and select cell link as well.
We can then click on the Up and Down arrow to navigate the from values form 1 to 12.
5. List Box – List Box can contain any range of list values. Select the option and draw anywhere in the sheet.
Right-click on any List menu and select Format Control. Then in the window of Format Control, select the Input range and Cell link. Here we have selected the range input range of numbers 1 to 5.
We will see, in the List box, all the numbers from the input range are seen. We can navigate to up and down and select and number. And the number will be seen in Cell Link B1.
6. Option Button – This is used when we need to select one option among others. Select and draw this anywhere in the worksheet.
Right-click on this and select Format Object. Now in the window below, select the Cell Link. We have select cell A2.
Now if we select this option box, reference cell A2 will be seen with value as 1. And unchecking it will show 0.
7. Group Box – This is used for multi controls. This only allow us to group other controls under it. Select and draw in worksheet.
Right-click on it and select Format Control as shown below.
To group different controls here, we first need to have Radio control which are customized.
8. Label – Label works just as a simple sticker. Its main purpose is to show the value or text entered in it as shown below.
9. Scroll Bar – With this, we can navigate the window up and down as we did in the List button. This is how it looks like below.
Right-click and select Format Object. From there select the minimum and maximum value and cell link as well.
By clicking on up and down arrow we will see the change in cell A3 which is our cell link.
Rest of the Controls are not activated. All those left controls are customized.
Pros of Form Controls
- All the mentioned controls are easy to implement. And we all have seen some of them in any form of our work.
- Format Control option is the same for most of the control. So it becomes easy to select the range and cell link.
Things to Remember
- The second part of Insert option which is ActiveX is only used for macros.
- Form Controls are used for both manual data and macros.
- Text Field, Combo List-Edit and Combo Drop Down-Edit are customized form controls.
- Form Controls can also be used with charts.
Recommended Articles
This is a guide to Form Controls in Excel. Here we discuss on How to use Form Controls in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
