You can give your worksheets form-like functionality without ever having to use VBA. In order to achieve this, you will have to use form controls. Form controls are accessed from the Developer Tab in Excel and they can greatly enhance one’s worksheet, report or dashboard.
So, let’s get started with a few simple examples, to illustrate how to use form controls in Excel.
Read More: Why We Use Form Controls on a Worksheet?
Adding the Developer Tab
1) First things first, in order to access the Developer Tab, go to File>Options>Customize Ribbon and make sure Developer Tab is checked, as shown below.
Using the Scroll Bar Form Control
1) We have two sheets, one called Scrollbar sheet and the other called SCValue.
2) In the sheet called SCValue, we have a range set up with a list of Student Ids and the corresponding Student Names, as shown below.
3) In order to insert a scroll bar on the sheet called Scrollbar, we go to Developer>Controls>Insert>Form Controls>Scroll Bar as shown below.
4) Insert the scroll bar in the same way one would a standard shape, and resize it as shown below.
5) Right-click the scroll bar and select Format Control.
6) Set the minimum value to 1, the maximum value to 30, the incremental change to 1, the page change to 10, and set the Cell link to $A$5. There are 30 Student Ids, in total on the SCValue sheet, so we want to have the same number for the scroll bar. Click Ok.
7) In Cell A7 on the Scrollbar sheet, enter the following formula and press CTRL-ENTER:
=VLOOKUP (A5, SCValue!A2:B31, 2, FALSE)
8) Upon pressing CTRL-ENTER, the student name of Anne Smith is retrieved by the VLOOKUP function. One can now use the scroll bar to scroll through all the Id numbers, and the VLOOKUP will update cell A7 with the student names as you scroll through the numbers, and cell A5 is updated. Cell A5 is our linked cell for our scroll bar, and also contains the lookup value for the VLOOKUP function.
So, by using the scroll bar and the VLOOKUP function in tandem, one can quickly go through all the Student ids without having to type them out one by one, and then the VLOOKUP will update with the corresponding student names as the user scrolls.
Using the Group Box and Option Buttons together
1) The Group Box is used to group a set of related controls (such as checkboxes or option buttons usually) into one unit.
2) In this case, we want to group three Option Buttons in the Group Box, since we want to indicate a list of mutually exclusive choices, to the user. Option Buttons are sometimes also referred to as radio buttons. In fact, if you have ever used HTML/CSS, you would be more familiar with the radio button terminology.
3) So, on the worksheet called GroupBoxAndOptionButtons, go to Developer>Controls>Insert>Form Controls>Group Box as shown below.
4) Draw a Group Box as shown below.
5) Change the Group Box 7 text, by clicking on the text and highlighting it, and typing Feeding Options, instead as shown below.
6) Go to Developer>Controls>Insert>Form Controls>Option Button.
7) Draw an Option Button in the Group Box as shown below.
8) Right-click the Option Button in order to select it, and highlight the text as shown and instead of Option Button 8, type Meat Only.
9) Follow the same process and add two more Option Buttons, making sure they are in the same Group Box, the text for the second Option Button should read, Vegetables Only, and for the third Option Button, Meat and Vegetables as shown below.
10) Right-click the Meat Only Option Button and select Format Control.
11) For Cell link enter, $A$9 and click Ok.
12) If the Meat Only, Option Button is selected, then a value of 1 is put into cell A9, if Vegetables Only is selected a value of 2 is put into cell A9, and if the Meat and Vegetables Option Button, is selected then a value of 3 is entered into the linked cell A9 as demonstrated below.
13) In Cell A11, input the following formula:
=IF(A9=1,”Carnivore”, IF(A9=2, “Herbivore”, “Omnivore”))
14) Upon pressing CTRL-ENTER, the value of Omnivore is returned by the If Function, since the Meat and Vegetables Option Button is selected.
15) If you select the Meat only Option Button, Carnivore will be returned in Cell A11, and if you select Vegetables only, the IF Function will return Herbivore.
16) The next step involves, right-clicking cell A9, and choosing Format Cells as shown.
17) Select Custom and type three semi-colons as shown below and click Ok.
18) So now even though the underlying value in the Cell A9 is 2 since the Vegetables Only Option Button is selected, it is hidden from the end user view, and just makes the sheet look neater. Now when the end user selects the option button choice, they don’t see the numbers changing in A9 because of the custom formatting, and thus will not be confused.
And there you have it.
Download Working File
Form controls can be very useful for adding form-like functionality to a workbook, without having to use VBA. Form controls make use of linked cells and the value in the linked cell is updated in some way, based on the actions taken with the form control at hand. Using formulas in combination with form controls, one can further extend the form control functionality and form-like appearance of the worksheet at hand.
Please feel free to comment and tell us how you use form controls in your workbooks or if you prefer using Active X controls.