7.5 Placing Controls Directly into Worksheets

Most of the controls introduced in the previous section can be used not only in forms, but directly in worksheets and charts . The exceptions are MultiPage and TabStrip for multipage forms, RefEdit for cell references, and Frame . Controls in worksheets make it possible to create tables that are very easy to use. Here are a few application examples:

You can provde a button to save or print the active worksheet or for some other frequently used process.
With a control box or option button various calculation options within the table can be selected.
A spin button can be used for conveniently setting the parameters of a calculation.
A listbox can be inserted for selection from among various calculational models.
A program can be centrally controlled by means of a group of buttons .

Pointer

Finally, we should mention that lists (databases) can be combined in worksheets with drop-down listboxes. These listboxes, however, are not inserted into the worksheet as controls, but are activated with DataFilterAutofilter and are managed directly by Excel.

Since with these listboxes we are dealing not with true controls, but with an aid for structuring data, autofilters will be discussed in the chapter on data management, Chapter 11.

Advantages

The greatest advantage of worksheets with controls, in comparison to their use in forms, is that they are much more flexible in worksheets. The user has the possibility of setting the visible range of the table with scroll bars. Problems that can arise with working with a large form on a laptop with 680 —480 pixels disappear when the control is in a worksheet.

There is also greater flexibility in the application. While a form must be closed before further work in Excel is possible, a jump from one worksheet to the next is always possible.

A further advantage for worksheets is that all the formatting and calculational features of worksheets can be used in parallel with the controls. For example, a chart can be updated according to the setting of a spin button.

Disadvantages

Where there are so many advantages, there must be some drawbacks. The most significant of these is that the controls in worksheets cannot be operated via keyboard input. There is no way of associating a control to a key or to move among controls with Tab. (This drawback should not be underestimated, since is applies precisely to those applications that are used frequently and thus should have maximal efficiency. The only solution is to construct procedures for keyboard management of the controls (property OnKey ), but that involves considerable programming effort.)

Another disadvantage is that worksheets with controls are unsuitable for being run by add-ins. (Add-ins are generally invisible and are usually run by forms. See Chapter 14.)

Some settings in controls are reset when an Excel file is opened. For example, if you select an element in a listbox, store the file, and then open it again, it can then no longer be determined which list element had been selected. Therefore, controls are usable only with restrictions for storing persistent data in worksheets.

The greatest disadvantage in the use of controls in worksheets is their lack of sufficient stability: With each new version of Excel ”97, 2000, and 2002 ”I have had a new set of problems to deal with. The worst, I am sorry to say, is version 2002, where I experienced not only problems with the functionality, but frequent crashes of the Excel program itself. These difficulties have compelled me to eliminate from this edition two examples that appeared in the previous edition that functioned flawlessly under Excel 2000. In view of this experience I am forced to recommend that controls not be used in worksheets if at all possible, despite all the attractive programming possibilities that these elements might offer.

Working Techniques

Most of what you have learned about the forms editor you can now forget. It is simply amazing in how many particulars working with controls in tables is inconsistent with the forms editor.

Inserting controls is accomplished as was done with forms. But working with controls is much more difficult, since Excel assumes every time you click on it that you actually wish to use the control. For this reason you can switch between a work mode and design mode with the Design Mode tool. The controls can be worked on only within design mode.

Tip

If you wish to insert controls into a worksheet, you must activate the Control Toolbox toolbar.

Take care that you do not accidentally activate the similar-looking "forms" toolbar. This toolbar contains the Excel 5/7 controls, which look the same but behave differently and are programmed differently as well.

There is no longer a simple mouse click for providing captions for controls. You have to select Optionbutton ObjectEdit from the pop-up menu. Now you can edit the text (Ctrl+Return adds a new line). The input is ended not with Return, but with Esc! (This goes against all tenets of good practice under Windows.)

Most of the rest of the properties can be set via the properties window. However, not all of the properties known from the forms editor are available. To top it off, many of the properites have different names (such as LinkedControl instead of ControlSource , and ListFillRange instead of RowSource ).

Tip	The selection of several controls can be accomplished quite easily by drawing a frame about them with the mouse after having first clicked on the Select Objects button in the Drawing toolbar.

Tip

It often takes considerable effort to place controls: All buttons should be as close to the same size as possible, arranged in a row, and be the same distance apart. Unfortunately, the Format commands known from the forms editor are not all available in worksheets. Some of the commands are hidden in the Drawing toolbar ( Align And Distribute ). If you wish to make several controls the same width or height, select the controls (mouse plus Shift instead of the otherwise usual Ctrl ) and input a numerical value for Width or Height in the properties window.

If in copying a button you wish to move in only horizontally or vertically, you can press the mouse button together with Shift+Ctrl .

Pointer

Further application examples can be found in Chapter 1 (literature database) as well as in Chapter 9 (templates): There, for example, a control box is placed to make it possible to switch between internal and external taxation (final amount with or without value-added tax, VAT) or to select one from among several rental car options (with differing price categories). Listboxes come yet again into Excel tables that are conceived as questionnaires. An example for setting up and evaluating such a form can be found in Chapter 12.

Formatting Controls

While properties specific to MS Forms are set via the properties window, there are some additional Excel-specific properties that can be edited in the dialog Format Control. (To be precise, we are dealing here with properties of the Shape object, which is used internally for embedding controls; see below.)

This dialog is invoked by way of the pop-up menu associated to the control. The settings of greatest interest are offered by the page called "Properties," where it can be determined how the size and position of the control change when column width and row height change. Moreover, here one may specify whether the control should be printed together with the rest of the table (the default is to print it).

Tip	Excel automatically uses the font (Font property) MS sans serif. This font, however, is very difficult to read when the zoom factor for the worksheet is less than 100 percent.You would be better advised to use the font Arial or Tahoma.

Tip

Worksheets, unlike forms, are badly protected against (often unintentional) alteration by the user. As soon as the format of a worksheet has been determined, you should protect it against unintentional alterations. To this end first deactivate the default activated protection for all cells and controls that should remain changeable (via the pop-up menu Format Control or Format Cells , followed by the dialog sheet "Protection"). Then activate the protection function for the entire sheet with ToolsProtection Protect Sheet .

Properties for Positioning Controls (Shape Object)

The embedding of controls in worksheets is carried out with Shape objects with Type=msoOLEControlObject (see also Chapter 10). The properties for positioning controls are thus based on the Shape properties: For each control the upper left corner ( Left and Top ) as well as width and height ( Width and Height ) are saved. These coordinates relate to the upper left-hand corner of the form or worksheet. TopLeftCell and BottomRightCell specify, furthermore, the cells beneath the upper left or beneath the lower right corner of the control

Placement determines how the control is to behave when changes to the worksheet are made. The setting xlMoveAndSize causes the control to be moved and resized when the worksheet is moved or resized. (The properties Left , Top , Width , and Height are thus automatically changed.) With the setting xlMove the size remains constant, but the location remains variable. With the setting xlFreeFloating the size and position are independent of the table's format; thus Left and Top do not change, regardless of how you may change the rows and columns .

Tip	All the properties listed here are valid not only for controls, but also for all other drawing objects (such as lines, arrows, OLE objects, embedded charts).

Access to Controls via Code

There are three ways of accessing controls in VBA code. The simplest approach can be used if you know the name of the control. Then, for example, you can access a button in a worksheet via Worksheet(n).CommandButton1 .

The other two possibilities are of use when you want to access all the controls of a worksheet in a loop. For this you can use the Shapes enumeration to test whether a given Shape object is a control object ( sh.Type = msoOLEControlObject ). In this case you can then access the control via sh.OLEFormat.Object.Object . ( OLEFormat refers to an OLEFormat object. OLEFormat.Object refers to an OLEObject . It is only with OLEFormat.Object.Object that you finally achieve access to the desired control element. OLE objects are used for embedding arbitrary objects and are discussed extensively in Chapter 14.)

 Dim sh As Shape For Each sh In Sheets(1).Shapes   If sh.Type = msoOLEControlObject Then     Debug.Print sh.Name, TypeName(sh.OLEFormat.Object.Object)   End If Next

Alternatively, you can loop over all OLE objects of a worksheet. When a particular OLE object is a control, then OLEType contains the value xlOLEControl . The control itself is then accessed via Object .

 Dim oo As OLEObject For Each oo In Sheets(1).OLEObjects   If oo.OLEType = xlOLEControl Then     Debug.Print oo.Name, TypeName(oo.Object)   End If Next

Communication Between Controls and the Worksheet

Communication between controls and the worksheet is carried out by a ControlFormat object, which can be addressed via the like-named property of the Shape object. The ControlFormat object is normally transparent. Its properties appear, to the extent that it makes sense, in the properties window of the control, and they can also be employed in program code as control properties ( affecting , for example, LinkedCell , ListFillRange , and PrintObject ).

Caution

In the default setting a button maintains the input focus after it has been clicked. In forms this is no problem. However, as long as the input focus is within some object of a worksheet, Excel will refuse to carry out all operations. Completely correct code therefore no longer functions correctly, and you can change neither worksheet cells nor the user interface. And on top of everything, the error messages that appear are completely useless.

Special Features of Buttons

A solution to this problem lies in the property TakeFocusOnClick=False . With this even when a button is clicked the focus remains where it was previously, and you can carry out all the operations of the event procedure. Why the default setting for this property is not False is a secret maintained by the wizards at Microsoft (or perhaps not).

The property TakeFocusOnClick exists, alas, only for buttons, though it is often needed for other controls. For example, if after a control box is clicked you would like the input focus to shift to a cell of the worksheet, you can add the following line to the Click event procedure of the control:

 Me.Range("C1").Activate

Me refers here to the worksheet in which the control is located (since the event procedure is located in the class module of the worksheet). C1 is an arbitrary cell. If you wish to move the focus to the cell next to the control, you can use the following command:

 CheckBox1.TopLeftCell.Activate

Special Features of Listboxes

The contents of a listbox can be set with the ListFillRange property. The result of the selection is written into the cell specified by LinkedControl .

In contrast to the Excel 5/7 listbox, the selected list text and not the index number is transferred to the ControlSource cell. Often, however, an index number is needed for further processing of the selection, in which case one should set BoundColumn to 0.

But then there is still a difference between the Excel 2000 and Excel 5/7 listboxes: The ControlSource cell contains values between 0 and ListCount-1 (instead of the previous 1 and ListCount ). You can take this into consideration in the further evaluation of the selection, but it is now no longer possible to distinguish between the first list entry (value 0) and no value at all (value Null ). The reason for this is that in Excel worksheets Null is occasionally interpreted as 0 (and sometimes also as the value #NV ). Unfortunately, I have no explanation as to why Excel interprets Null in different ways as the mood strikes it.

The next feature concerns initialization: When a worksheet with a listbox is loaded, no entry of this control is activated. ListIndex has the value “1. If this undefined state is unwished for, then a definite state must be established in Worksheet_Open (object "This Workbook"). For example, with ListIndex=0 the first entry can be activated.

Caution

Listboxes with ListStyle=fmListStylePlain (that is, in the default setting) are sometimes erroneously displayed; that is, not all list entries are visible. You can solve this problem with ListStyle=fmListStyleOption, which displays the list entries as option buttons.

Program Code

The event procedures associated to the controls are to be found in the module belonging to the associated worksheet. If you click on the tool VIEWCODE or select the like-named command from the pop-up menu, you are transported into the development environment, where the relevant instructions Sub and End Sub are immediately inserted (in the case of a new control, that is; for a control that has already been programmed, its code is shown).

The Start Menu for Userform.xls

The sheet "mainmenu" in Userform.xls is command central for this workbook (see Figure 7-25). Actually, this sheet contains only a few buttons. Its attraction is in the visual formatting of the buttons and the formatting of the background, which leads the user to forget that this sheet is in reality an ordinary worksheet.

Figure 7-25: Command Central for Userform.xls

The event procedures associated to the buttons are quite simple, so that we have provided here only one example:

 ' Userform.xls, Object "sheet1" Private Sub  btnFrame_Click()  dlgFrame.Show End Sub

For the visual presentation of the worksheet all cells were simply provided with a background color. Under the buttons was placed a rectangle with rounded corners in a contrasting color . The rectangle was added as an AutoForm object ("Drawing" toolbar) and placed under the buttons with the pop-up menu entry OrderSend To Back. Furthermore, with ToolsOptions the row and column headers and the gridlines were made invisible.