The basic of Form Controls

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 RibbonExcel Tips - Form Controls 1.PNG

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 ControlsExcel Tips - Form Controls 2.PNG

Now you know where they are.

So What? are they?

Excel Tips - Form Controls 3.PNG

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.

Excel TIps - Form Controls 4.gif

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.

Excel Tips - Form Controls 5.PNG

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):Excel TIps - Form Controls 8.gif

How about other forms?

Let’s go through them one by one.

Check Box

Excel Tips - Form Controls Checkbox.PNG

How it interacts with the cell linked?  

It simply returns TRUE or FALSE.

Excel TIps - Form Controls CheckBox.gif

Option Buttons

Excel Tips - Form Controls Option Button.PNG

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.

Excel TIps - Form Controls Option Button.gif

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:

Excel TIps - Form Controls Option Button1.gif

Spin button and Scroll Bar

They have more controls.  You need to set Min and Max Value, as well as Incremental change.

Excel Tips - Form Controls Spin Scroll.PNG

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.

Excel TIps - Form Controls Spin Scroll.gif

 

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.

 

Wait… What about “Label”?

Well, I have no idea why and when I need to use “Label”.  Why not just use Text Box?

Advertisements
This entry was posted in Excel Tips and tagged . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s