How to use Form Controls in Excel

Form Control is a set of special objects in Excel. These objects when inserted into the Workbook will allow us to create:

1. List box.
2. Check box to control the Show/Hide of one or several objects.
3. Increase/Decrease button to control the value of a cell in Worksheet…

There are all 9 Form Controls in Excel, which are: Button, Combo Box, Check Box, Spin Button, List Box, Option Button, Group Box, Label and Scroll Bar

Yes, and in this article I will show you how to use all the Form Controls listed above, except Button and Label.

For these 2 Form Controls because I have never used them, I can't give you more detailed instructions, so please understand… I will add more later.

#first. Add Developer tab in Excel

Method 1:

+ Step 1: Select File => select More… => select Options

+ Step 2: The Excel Options dialog box appears, then select Customize Ribbon => tick Developer => select OK

Method 2:

+ Step 1: Right-click File => select Customize the Ribbon.

+ Step 2: Then do the same as Step 2 in Method 1, guys.

#2. Steps to insert Form Controls in Excel

+ Step 1: Select Developer => select Insert => choose 1 of 9 Form Controls that Excel provides.

+ Step 2: Click anywhere in the Worksheet to insert.

#3. Some basic operations with Form Controls

As mentioned at the beginning of the article, these Form Controls are special objects so you cannot manipulate them like normal objects.

3.1. Resize Form Controls

Method 1:

Right click on Form Controls => then 6 'white circles' will appear drag and drop to resize.

Method 2:

+ Step 1: Right-click on Form Controls => select Format Control…

+ Step 2: In the Format Control box => select the Size tab => re-enter the size in the Height and Width boxes => select OK chọn

3.2. Move Form Controls

Right-click Form Controls => press ESC key on the keyboard => when the mouse pointer turns into a '4-way arrow', drag and drop the mouse to move.

3.3. Delete Form Controls

Right-click on Form Controls => press the ESC key on the keyboard => press the Delete key on the keyboard.

3.4. Open the Format Controls dialog box

Right-click on Form Controls => and select Format Controls.

In addition to the above method, you can also select Form Controls => and then select Properties on the Developer tab.

In this section, I cannot guide you in detail how to format the Form Controls, because each Form Controls will have different formatting options.

For details on how to format see part #4 below.

#4. The Most Used Form Controls in Excel?

In this section, I will present the 4 most used Form Controls. Specifically, I will describe the function, customization instructions and illustrative examples.

Combo Box and List Box; Spin Button and Scroll Bar have similar functions and customizations, so I only guide one Form Controls. Specifically, I will guide Combo Box and Spin Button.

4.1. Combo Box/ List Box

This Combo Box allows us to create a list, from which the user can select any item in this list.

List items are defined by the data in the cell block. This block of cells will be declared in the Format Controls dialog box.

The return value at the linked cell is the ordinal number of the items in the list:

+ Step 1: Create Combo Box

+ Step 2: Open the Format Controls dialog box => select the Control tab

1. Input range: Select the cell block containing the list.
2. Cell link: Select the link cell.
3. Drop down line: Select the number of lines to drop down.

+ Step 3: Select OK

4.2. Check Box

Details on how to use this Form Controls have been presented in the article Create a Check Box to control Conditional Formatting, please review if you have not read that article.

4.3. Spin Button/ Scroll Bar

Spin Button is one of the easiest to use Form Controls. With this Form Controls you can click on the 'black triangle buttons' to increase or decrease the value of the cell.

In the Format Controls dialog box, select the Control tab

1. Current value the current value.
2. Minimum value is the smallest value.
3. Maximum value is the maximum value.
4. Incremental change increment/decrement value on one time.

4.4. Option Button and Group Box

Option Button is never inserted individually but will be inserted in pairs or groups of many different Option Buttons. These Option Buttons will be grouped into a Group through the Group Box.

In the same Group at the same time, only one Option Button can be selected:

+ Step 1: First, insert the first Option Button.

+ Step 2: Then, right-click and select Edit Text to rename.

+ Step 3: Repeat Step 1 and Step 2 to create the remaining Option Buttons.

+ Step 4: Continue to insert Group Box

+ Step 5: Right-click on Group Box => and select Edit Text to rename…

+ Step 6: Continue to right-click on the first Option Button => select Format Control

+ Step 7: In the Format Control dialog box, select the Control tab => at Cell link select the cell to be linked. Here I will link to cell A3

=> The remaining Option Buttons will automatically receive cell A3 as a link cell.

Normally, Form Controls in general or Option Button and Group Box in particular only come into play when it is combined with a certain formula.

For example, in this example, I will assign the formula =IF(A3=1,"Excellent", IF(A3=2,"Good",IF(A3=3,"Good", 'Average')) ) for cell B1.

=> When the user selects any Option Button in the Group Box, cell B1 will automatically appear his graduation rank.

#5. Epilogue

Okay, so I just shared with you the knowledge about Form Controls, as well as how to use Form Controls in Excel.

Yes, Form Controls is one of the advanced features in Excel, because it is advanced so it is quite confusing and difficult to use. However, this is a great feature and it will help you a lot for your work.