Sam On Forms Controls

Today we have another guest post from Sam Radakovitz (Sam brought you a number of posts on the Trust Centre as well as an Excel 2007 game).  Today, Sam talks a bit about Forms Controls and an example of the sort of thing you can do with them.  Note – this is possible in current versions of Excel too. 

This one off blog post is about forms controls.  Many people don’t know it, but there are quite a few things you can do with the forms controls in Excel.   I’ll start off by giving a brief description of the controls:

Forms controls are a collection of common controls (check box, list box, scroll bar, ect…) available for use in the Excel grid.  Some people mistake the forms controls for Active X controls, but they are very different.  For one, they aren’t Active X controls (I bet you saw that coming) … they are essentially “Office Art” shapes.  This means they are lighter-weight than an Active X control and aren’t checked by security or disabled in the application, and definitely don’t count as macros.  For two, they provide nice integration into the Excel grid, allowing linking of particular properties to cells.  The linking is the key to these controls, with that you can react to actions performed with the control in Excel formulas, which is pretty darn powerful.

Here’s an example of a scroll bar scrolling cells in the Excel grid:

Let’s look at the formulas that are driving this:

As you can see, we’re using offset to look into the game list based on the scroll link, and the scroll link is automatically changed by the scroll bar when it is used.  Next, let’s look at the properties for the scroll bar:

The key here is the Cell Link … once you set this property, the scroll bar will put its position in that cell.  Having that, the offset formula in our fake list box works like a charm!

To download the above example, click here.

As mentioned above, there are quite a few different forms controls, all fairly standard (checkbox, option group, list box, … ) that are available for use.  In previous versions of Excel you have to enable the forms toolbar to add the controls, in Excel 2007 they are located on the developer tab.

PS Updated the images to be jpg, not bmp