Blog Archives

Excel – Using Form Controls

From time to time you may come across tick boxes, option buttons or similar on a worksheet. These seem to be little objects that appear to do some clever things in formulas or to objects on your worksheet such as charts. These are not as difficult to set up and use as you might think.

First of all, you need access to FORM CONTROLS.

Make sure your DEVELOPER tab is turned on (2013 – right click on the RIBBON and select CUSTOMIZE THE RIBBON and tick the DEVELOPER box/2007 – click the OFFICE button, then select EXCEL OPTIONS and tick the third box down – SHOW DEVELOPER TAB IN RIBBON).

From the DEVELOPER tab click on INSERT. This will show you all the available controls.

ScreenHunter_187 Jan. 22 14.37

 

Make sure you choose from the top half i.e. FORM CONTROLS and not from ACTIVEX CONTROLS or you’ll be expected to start writing VBA code to make them work! If you want to, right click over the icons and select ADD GALLERY TO QUICK ACCESS TOOLBAR. This will then give you a shortcut to the controls.

Shortcut added to the QAT

Shortcut added to the QAT

Now you have access to the controls, let’s start using them.

The controls at your disposal are;

  • Buttons
  • Combo boxes
  • Tick/check boxes
  • Spin buttons
  • List boxes
  • Option buttons
  • Group boxes
  • Scroll bars

There are a few others there but the label option is pointless as you can use INSERTTEXT BOX, and there are a few greyed out buttons too, that no one seems to know why they exist on the toolbar if they can’t be used.

So how do you use these FORM CONTROLS?

Buttons:

Click on the BUTTON icon then click and drag on the worksheet to draw your new button. Don’t worry if it’s not the right size you can always adjust it afterwards.

As soon as you draw the BUTTON a new window will appear prompting you to assign a macro to the BUTTON.

Window to assign a macro to a button

Window to assign a macro to a button

Select a MACRO from the list and click on OK. If you don’t know what macros are, then you won’t need any buttons.

Any BUTTON you add will be given a default name…Button1, Button2 etc. Once you’ve added your macro, edit the caption on the button to let users know what it does.

Edited button aption

Edited button caption

Click away from the button, and it is now active and ready to use. Just click the button to make it run the macro for you.

If you need to adjust the size of the button or change the caption hold down the CTRL button and click otherwise you’ll just run the macro. Make sure you release the CTRL button once you’ve clicked on it or you’ll create a duplicate if the mouse moves while the CTRL button is being held down.

Tick/Check Boxes:

Along with buttons, this is probably one of the more likely controls you are going to use. As with the BUTTON, click on the icon in the toolbar and draw it on your worksheet.

Basic check box

Basic check box

You can edit the caption and resize if necessary. However, a tick/check box is purely a visual object. By that I mean it does nothing by itself other than look pretty. In order to make it useful, you need to right click on it and select FORMAT CONTROL. All the usual formatting options exist and you can play with those to discover what they do. The important tab to look at is the CONTROL one.

Control tab for a tick/check box

Control tab for a tick/check box

Set the default value (read appearance) of the tick/check box. Mixed is an odd one…the box just appears filled in.

The Mixed option on a check box

The Mixed option on a check box

The most important bit here though is the CELL LINK. Select a cell that will contain the value associated with the choice made with the TICK/CHECK box. Ticked/checked is equal to TRUE, and un-ticked/unchecked is equal to FALSE.

Ticked/un-ticked values

Ticked/un-ticked values

It is the value in the selected CELL LINK that you then use in your formulas. The TICK/CHECK box is simply an interactive image. Without the CELL LINK, the TICK/CHECK box does absolutely nothing of use other than display a tick or no tick.

Option Buttons:

Setting these up is exactly the same as the TICK/CHECK box. Draw it, right click, and assign a CELL LINK. As you add more OPTION buttons they are all linked to the same cell. Depending on the OPTION button you select a different number will appear in the cell.

Selecting Option butons

Selecting Option butons

What if you want to create a set of separate OPTION buttons that are not linked to the first set?

This is where you have to use a GROUP BOX.

Draw a GROUP BOX, add some OPTION buttons inside it, select any one of the buttons, right click and assign a CELL LINK. All the other buttons within the group box will then link to the same cell. You will now have two independent sets of options to choose from.

Independent option groups

Independent option groups

Combo Boxes:

Follow the same routine to set these up as above. The difference with these is that you need to have a set of source cells that contain information you want to see appear in the list. This is the INPUT RANGE on the CONTROL tab. Select a CELL LINK too. DROP DOWN LINES refers to the number of items to be displayed in the box. Beyond that scroll bars need to be used to see the rest of the items in the list.

Control tab for Combo boxes

Control tab for Combo boxes

When you use the COMBO BOX, the INPUT RANGE appears in the drop down, and when you make a selection from the list, the CELL LINK will display the position of the selected item as a number.

Input range and Cell Link for a Combo Box

Input range and Cell Link for a Combo Box

Spin Buttons:

Usual set up…go to the CONTROL tab.

Set a current value, minimum and maximum values, as well as a value for incremental change i.e. each time someone uses the up or down button, how much does the value change by? Make sure you set a CELL LINK as with all the others.

Control tab for a Spin Buttons

Control tab for a Spin Buttons

Sequence of spin buttons

Sequence of spin buttons

Obviously, you can set any min/max and increment you want…entirely down to what you want to display.

List Boxes:

Wheras the COMBO BOX gives you a drop down list, a LIST BOX is exactly what is says – a box that contains a list. So when you draw the box, make sure it is big enough to display at least part of your list. Scroll bars will appear if the list is longer than the box.

There are a few options in the CONTROL tab that we have not seen yet.

Control tab for a list box

Control tab for a list box

INPUT RANGE and CELL LINK are identical to the other controls but you have options relating to SELECTION TYPE. In reality, only the SINGLE option is viable. Although you can select multiple items in the list, the CELL LINK can only store one value. EXTEND allows you to click and drag across multiple items to select them, but again, same problem applies to the CELL LINK. To make use of MULTI and EXTEND options you need to apply some VBA coding.

Scroll Bars:

Usual set up applies…SCROLL BARS can be vertical or horizontal. The settings for SCROLL BARS are identical to SPIN BUTTONS. Just go to the CONTROL tab and set everything there.

So setting all these controls is fairly simple. They all pretty much follow the same steps. The next step of course is using them in a useful way.

It is probably tempting once you know how to set these up in a worksheet to put as many FORM CONTROLS as is humanly possible into your work. However, be warned…the more controls you put in the more possible combinations you need to deal with.

Let’s take a simple example: you have two tick/check boxes and four option buttons in one group. Seems simple enough but what are all the possible choices that someone can make?

Combinations possible with 2 tick boxes and one option group with 4 option buttons

Combinations possible with 2 tick boxes and one option group with 4 option buttons

Looking at the table above, each TICK/CHECK box can be TRUE (T) or FALSE (F). We can choose only one option out of four. If an option button is selected then this is shown with a Y and N if not selected. What started as a good idea, quickly becomes a major problem, as you now have to write nested IF statements that can cope with any of the 16 possible outcomes. Imagine adding another option group or a combo box into the mix and how that might affect the total number of possible combinations! Unless you are very confident with creating long nested functions this is a non-starter. I’m not saying don’t do it….but think about what you need to do to manage it all in your spreadsheet. Here is a basic example of a nested IF statement to handle a single tick/check box and two option buttons…

=IF(AND(I1=2,J1=FALSE),SUM(A3:A18),IF(AND(I1=2,J1=TRUE),SUM(A3:A18)+SUM(B3:B18),COUNT(A3:A18)))

Gives you an idea of what you are facing, the more controls you add.

So FORM CONTROLS are a great way to add some sort of clever interaction in a spreadsheet. They can help to show/hide information, perform calculations based on criteria or can help you build dynamic charts. All I’ll say is don’t get carried away as the number of possible outcomes increases very quickly with each additional control that you add, but certainly a set of tools worth taking a look at.

Advertisements

Interactive Excel Voting Board

If you run a training session, or perhaps you chair a meeting, you want to get some instant feedback, or perhaps you want attendees to vote on something. You can do a simple show of hands which works fine, but how about something a little more high tech but without going overboard?

Ideally, for this to work, and in particular to get people involved you will need a touch screen. You could just use a mouse on the computer but this seems to work quite well and gets people out of their seats participating in the session.

There are two ways I can do this:

  • The non VBA way
  • Using VBA

Let’s start with the non VBA way using FORM CONTROLS.

If you are using 2003 then you will need to activate your FORMS toolbar. Go to VIEW, TOOLBARS and select FORMS. Do not use CONTROL TOOLBOX which looks very similar as this requires the use of VBA to make things work.

If you are using 2007 or later, turn on the DEVELOPER tab and you will find FORM CONTROLS under INSERT in the CONTROLS group.

ScreenHunter_109 Jun. 14 12.07

Select the SPIN BUTTON option. Your cursor will change to a small cross-hair. Click and drag somewhere in your worksheet to “draw” the SPIN BUTTON. You can move and resize this at any time if it’s not quite right so don’t worry if it does not look quite right yet.

ScreenHunter_109 Jun. 14 12.08

Right click on the new SPIN BUTTON and select FORMAT CONTROL.

ScreenHunter_110 Jun. 14 12.08

In the dialog box decide what your minimum and maximum are and also the increment that each click of a button will increase/decrease the value by. So in this example I will set the minimum to 0, the maximum to 10 and the increment value to 1.

The final thing you need to set is a reference cell or CELL LINK. The form controls do nothing other than look pretty. In order for them to work, you have to link them to a cell and it is this cell that will do the work.

ScreenHunter_110 Jun. 14 12.27

Click on OK.

Now create a chart based on the value in the CELL LINK.

Format your chart whichever way you want and repeat as many times as necessary to create multiple charts to display people’s votes.

ScreenHunter_110 Jun. 14 12.33

Now for the VBA version. If you are familiar with VBA then this is a very simple way of doing things, and if you aren’t familiar then this is simple enough to have a go.

The benefit of VBA over the FORM CONTROLS is you have more options available to you and you can, if you want to, go mad and program it to do all kinds of weird and wonderful stuff…but for now, here is a simple example for you which you can embellish as your VBA skills grow.

This time start by entering the value 0 in a cell.

Now add a button to the worksheet, but this time use the ACTIVE X CONTROLS, NOT the form controls we used in the previous example.

Click on the BUTTON icon then click and drag to “draw” a button on the worksheet. Again this can be moved at a later stage if it’s not in the correct position.

Click on the PROPERTIES button and do the following:

  • Give a suitable name to your button (helps when writing code)
  • Enter a caption i.e. what you want to display on the button face

There are many more options you can go for but this would be the bare minimum at this stage.

ScreenHunter_110 Jun. 14 15.10

Close the PROPERTIES window for now.

Now we need to add some code to our button to make it work. Double click the newly added button, making sure that you are in DESIGN mode in order to be able to make changes to your button.

ScreenHunter_110 Jun. 14 15.15

 

I declare a PRIVATE variable in my GENERAL DECLARATIONS area at the top of my code sheet. I need this to store the value of the cell that I am associating with the button and subsequent chart.

I can then add the following bit of code:

ScreenHunter_110 Jun. 14 15.23

 

Again, repeat as many times as necessary to build up your voting board.

So far though, we only have a button that increases the value of the cell. We now need to add one that takes the value down by one each time we click on it. At this point you are probably thinking that this is a bit long winded, considering we had a single button that did both before. Using this method means you can have a more customised look and feel to your voting board rather than just up/down arrows (the choice is yours!)

Follow the same steps as before to add a new button and then add the following code:

ScreenHunter_110 Jun. 14 16.40

 

This time as well as decreasing the value of cell C6, I have added an IF statement to prevent negative values from being generated.

The final button you might want to add is reset button so you can quickly reset the value(s) to 0. This might be worth doing last of all once you have created all the voting buttons and charts you need and then have a single button to reset all the reference cells in one hit

.ScreenHunter_112 Jun. 19 16.15

 

Simply add extra cell references once you have set them all up e.g. Range(“C6, F6, I6”).Value = 0

Your final interactive voting tool will look something like this…

ScreenHunter_111 Jun. 19 16.14

 

So next time you want to have a poll in class or at a meeting you can do it with a little more style than just a show of hands.