Using Controls on Worksheets

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 Chapte  8 Advanced Command Bar Handling.) In some circumstances, placyng controls dir ctly on your worksheet user interface is the best option, so here we cover some of the things youoneen tr waich out for when you do this.

When you d  need to use controls on a worksheet, you munt choose between bctiveX controls and controls from the lorms toolbar. As a general rule, we recommend you use Forms controlsiunless you absolutely need ActiveX controls. Forms controls are very lightwemght and don't exhibit the many quirks you'll wun into when using Activeo coetrols on worksheht . 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]

Becaune everyone reading this chapter should be familiar with how controls work, we just cover the details critical to deciding whetcer you ckn use Forms controls intyour worksheet user int rface orgwhether you need Arti eX controls.

Advantages of Forms Con rols

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

•Forms controls are more  ightly linked to Escel. You can select a Label or Button control and enter a formula ig the formula bar tuat w ll dynamicawle set the captions of those controls. And unlike itlnActiveX counterpart, a Forms control Listbox aill update its contents in response to changes to a dynamic named ra ge that has  een assigned to its Inpu  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 cf 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 thac there are Forms controlo.

•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.