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.

Advertisements

Posted on June 20, 2013, in General, VBA and tagged , , , , , . Bookmark the permalink. 2 Comments.

  1. When I get to the Format Control pop up window I do not have the fifth tab labeled /Control\.

    • I suspect you have added an Active-X control rather than a form control. Make sure you use the controls in the top half of options. By using the FORM controls you will then see the fifth tab “control”.

Leave a Reply

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

%d bloggers like this: