Excel worksheet interactivity with ActiveX controls

Using the Control Toolbox we can create interactive worksheets without any VBA whatsoever.

Commonly called ActiveX controls, one application for them is to link them to worksheet cells. This allows you to put controls on your form, capture user input (typically in a hidden section of the worksheet), and make worksheet-level calculations. Here are short descriptions of many of the available ActiveX controls.

Download sample workbook

Check box control

The check box control returns TRUE when checked, and FALSE when not. When linked to a cell, the cell will display either TRUE or FALSE accordingly.

Combo box control

The combo box control can be made to read a list of values from the worksheet to populate the combo box, and read the user selection or entry back to the worksheet. Unfortunately you can't restrict choices to just those found on the list (as you can in Access, or with Excel's Data Validation feature).

List box control

Like the combo box control, the list box control can read a set of values from the worksheet to populate itself, and write the selection to the worksheet. But it does not allow selections or entries that are not on the list. So it's ideal when the user must select something and should not be allowed to select anything else.

Option button control

Like check boxes, option buttons return TRUE or FALSE to the worksheet. You can also see a toggle button in the screenshot above, which we'll explain below.

Scroll bar control

The scroll bar control can be set to minimum and maximum values, which are cycled through in the linked cell when the scroll bar is moved. Set these to the range needed for your particular application.

Spin button control

The spin button control is similar to the scroll bar, except you click a button to change the linked cell, instead of moving a button across a scroll bar.

Text box control

The text box control takes it input and places it into the linked cell.

Toggle button control

The toggle button has two states: up or down. When depressed (down), the value returned to the linked cell is TRUE. When the button is up, the value returned to the linked cell is FALSE. I used a toggle button with the option button because otherwise there's no way to reset the option button; you can only click it once. The toggle button changes the value of the linked cell to FALSE, which effectively clears the option button control.

Sample controls form

To demonstrate how you might combine these controls together into a worksheet "form", I created a sample form. All I did was use the Drawing toolbar to draw a rectangle on the worksheet. After adding a shadow (Shadow Style 6, if you must know), I added some linked controls on top of it, and moved them to the top of the display order. To do that, right-click each object and choose Order from the context menu. Then Ctrl+click all of the controls and group them (on the Drawing toolbar, go to Draw » Group). Now you can drag the whole thing around as a grouped set of controls.

All of the controls on the sample form are linked to the same cells as the other controls, so you can change values on the form and watch the other controls adjust themselves!

Debra Dalgleish wrote about combo boxes on her blog (see 3 Types of Excel Drop Down Lists Compared ), and Jan Karel Pieterse has an article about ActiveX controls as well. Do you know of any other good articles?

Download sample workbook

Related Articles:

• February 28, 2009 -- Clear out all controls on a form