Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA® نسخه متنی

Using Controls on Worksheets

Using controls placed directly on worksheets is typically not the best user interface design. For most Excel application development, we recommend you use custom command bars as entry points into your code and substitute data-validation lists for combo box controls. (Command bars are given a full-chapter treatment in Chapter 8 Advanced Command Bar Handling.) In some circumstances, placing controls directly on your worksheet user interface is the best option, so here we cover some of the things you need to watch out for when you do this.

When you do need to use controls on a worksheet, you must choose between ActiveX controls and controls from the Forms toolbar. As a general rule, we recommend you use Forms controls unless you absolutely need ActiveX controls. Forms controls are very lightweight and don't exhibit the many quirks you'll run into when using ActiveX controls on worksheets. Figure 4-32 shows a worksheet in which Forms controls have been used to great effect.

Figure 4-32. Good Use of Forms Controls on a Worksheet

[View full size image]

Because everyone reading this chapter should be familiar with how controls work, we just cover the details critical to deciding whether you can use Forms controls in your worksheet user interface or whether you need ActiveX controls.

Advantages of Forms Controls

Forms controls can be used on Chart sheets, ActiveX controls cannot.

Forms controls are more tightly linked to Excel. You can select a Label or Button control and enter a formula in the formula bar that will dynamically set the captions of those controls. And unlike its ActiveX counterpart, a Forms control Listbox will update its contents in response to changes to a dynamic named range that has been assigned to its Input range property.

It is a simple matter to assign multiple Forms controls to run the same VBA procedure. Doing the same with ActiveX controls requires a more complicated class-based approach.

If you use multiple windows or the split-panes feature in your application to show two different views of the same worksheet, ActiveX controls will only work in the original window. Forms controls will work in any window.

Advantages of ActiveX Controls

You can modify the appearance of ActiveX controls to a much greater degree than Forms controls.

There are more varieties of ActiveX controls than there are Forms controls.

ActiveX controls have a wide variety of event procedures that you can respond to, whereas Forms controls can only run a single macro.

NOTE

If you use ActiveX controls on worksheets in Excel 97, you will frequently run into problems with VBA code that will not run in response to a control being selected. This is because the selected control has "stolen" the focus from the worksheet. With the exception of the CommandButton control, which has a TakeFocusOnClick property that can be set to False to eliminate this problem, the first line of code associated with an ActiveX control on a worksheet under Excel 97 should select a cell on the worksheet to return the focus to it.