Want to work like an Excel Pro? Learn more about Form Controls.
Have you seen someone using Check box, Option Buttons, Scroll Bars on their worksheets and be impressed? You may wonder how they made it and think that is something requiring professional Excel skills…
Indeed, it is easy to insert Form Controls and use them to interact with data on worksheet.
You may download a Sample File to follow through.
First thing first,
Where is Form Controls?
First, you have to add the Developer Tab. Please don’t be afraid of the word “Developer”. You don’t need to know a single line of code in order to use Form Controls.
Go to File –> Options –> Customize Ribbon
Check the “Develop” and OK. Then, you should see the Developer Tab on your ribbon.
Go to Developer Tab –> Insert , where you find the Form Controls
Now you know where they are.
So What? are they?
There are 9 Form Controls you can select from. The final three are inactive and they are for Excel version 5.0 dialog sheets only.
You may find the official descriptions HERE so I am not going to repeat them. I believe it you may be more interested in learning about HOW to use these From Controls to interact with your data.
How to insert?
Just select the Form Control you need; then move your cursor to where you want to insert, click on it. As simple as that.
Note: You may edit the size by drag and drop; or edit the text anytime you want.
How to make Form Controls Interactive?
Basically you can Assign Macro to any of the Form Controls.
Nevertheless, the benefits of using Form Controls is to interact with cell data without VBA. And this is the focus of the post.
Interact with data – Format Control
You may have noticed that after you have inserted Form Controls, you can click on it but nothing happens. Yes, nothing will happen until you “Format Control…”
Combo box and List box
For Combo box and List Box, you need to assign an Input Range, a list on your worksheet that you want to feed into the Combo Box or List Box…
Note: Make sure you are on the “Control” tab in the Format Control dialogue box. However, you won’t find “Control” tab for Button and Label.
How they interact with the cell linked?
You may expect that they return the value selected. However, they returns the position of your selection in the list. (Observe the change in A1 and E1):
How about other forms?
Let’s go through them one by one.
How it interacts with the cell linked?
It simply returns TRUE or FALSE.
How it interacts with the cell linked?
It returns an index number according to the order you placed the Option buttons; even the newly added Option button.
However, if you want to insert a new Option Button that is independent to the existing group of buttons, you will need Group Box. See demo below:
Spin button and Scroll Bar
They have more controls. You need to set Min and Max Value, as well as Incremental change.
- Scroll Bar allows “Page change“, i.e. a big jump in numbers (see demo below)
- You may place a horizontal Scroll Bar… but there is no way to make a horizontal Spin button… 😦
How they interacts with the cell linked?
They return a whole number in the range of the Max and Min value set.
As you see, inserting Form Controls and using them are easy. I would suggest you play with the various Form Controls and get yourself familiarize with how they interact with cell data. It requires some efforts and thoughts to apply them in your Excel sheets. Let’s talk about some examples later. Stay tuned. 🙂
Meanwhile, below are some posts with examples using Form Controls.
- Display % of column and % of row in a dynamic way
- Interactive Chart with navigation panel [NO VBA]
- Want some pies?
Wait… What about “Label”?
Well, I have no idea why and when I need to use “Label”. Why not just use Text Box?
How do you do expand a table which has form controls without having to adjust the cell links one at a time? I’ve tried everything with the format control box i.e. using the “$” in when referencing the cell to no avail.
Hi FenderT, would you please elaborate a bit to help me understand better what you want to achieve ?
I have a table in excel which has about 30 rows and 10 columns . 5 of the 10 columns are cells with a “spin button” ( up and down arrow) that I linked to its adjacent cell.
I need to expand the column (prolly to 100 rows) however when I copy the one row all the controls are still linked to the cell referenced from the row it was copied from.
Which means I would have to re link each button to it’s new adjacent cell. So far that’s what I’m doing. It will take up a lot of my time.
I see. You will need some vba cosesy to do that. You may take reference for the following post by Debra.
Hope it helps.
Check this. Hope you like it. 🙂
Hey mate this is very nice!, woud you mind sharing the file from the first picture? Like how do you highlight the bars on focus ? (on the right side chart). Hope to hear from ya!