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?

Assign Macro

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.

Check Box

How it interacts with the cell linked?  

It simply returns TRUE or FALSE.

Option Buttons

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.

Notes:

1. Scroll Bar allows “Page change“, i.e. a big jump in numbers (see demo below)
2. 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?