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

User Interface Drawing Techniques

Using Borders to Create Special Effects

To keep the user focused on the elements of your worksheet user interface, it is often helpful to modify the normal style so all unused areas of the worksheet have a consistent, light gray background color. This practice has been demonstrated in most of the user interface examples shown so far and will be used in our sample application. On top of this light gray background, you can use cell borders to create some interesting special effects. One of the most commonly used border-based special effects gives a range of cells a 3D appearance, either raised or sunken. Figure 4-13 shows examples of both effects.

Figure 4-13. Using Borders to Create 3D Visual Effects

To create a raised effect, you just add a white border to the top and right sides of your range and add a 50 percent gray border to the left and bottom sides of your range. To create a sunken effect, you do exactly the opposite. The width of the borders can be used to control the degree of the effect.

When you've applied a background color to a worksheet, as we've done in the example above, Excel's standard gridlines are obscured. In many cases gridlines are a useful visual guide for the user, so you want to put them back. Although there is no way to force Excel's standard gridlines to display over a background color, you can easily simulate gridlines by adding 25 percent gray borders with the lightest width to the area where you want the gridlines to appear. Figure 4-14 shows this effect.

Figure 4-14. Using Borders to Simulate Gridlines

Creating Well-Formatted Tables

Tables used within an Excel worksheet user interface typically have one or more of the following elements:

Table description

Row and column descriptions

Data-entry area

Formula result area

Each section of your table should be formatted with a unique style that you use consistently throughout your user interface. Figure 4-15 shows a sample table with all four of the elements described above.

Figure 4-15. A Basic Worksheet User Interface Table Layout

As you can see, in its simplest form the table is not very attractive. You can give your tables a much more professional appearance by using borders to provide a 3D effect and simulated gridlines and by increasing the row heights and column widths to provide more visual separation. Turning off the row and column headers and the formula bar completes the effect. The table now looks like a completely custom user interface. Figure 4-16 shows the table with these added effects.

Figure 4-16. A Fully Formatted Worksheet User Interface Table

Cell Comments for Help Text

Cell comments are one of the most important user interface features provided by Excel. Their utility stems from the fact that in many cases they can serve the same purpose as a help file without requiring users to do anything more complicated than hover their mouse cursor over the commented cell. Note that cell comments have several limitations that may make them inappropriate in certain situations:

If you are using the freeze panes feature on a worksheet and the worksheet is scrolled beyond the freeze point, if the comment window overlaps the frozen row and/or column it will be cut off at the point where the window is frozen.

Each cell comment is also associated with a specific status bar message whose structure cannot be modified. The status bar message displayed when users hover their mouse over a comment has the following structure, which is shown graphically in Figure 4-17:

Figure 4-17. The Format of an Excel Comment Status Bar Message

Cell address commented by username at the time the comment was created

The only part of this message you can modify is the user name at the time the comment was created section, which displays the contents of the User name entry located under the Tools > Options > General tab of the Excel menu. If you are a consultant creating a worksheet user interface for a client, it's unlikely your client will want to see your name in the status bar each time they view a cell comment. In that case, one of the best workarounds is to change the User name setting on your machine to your client's company name while you create the comments for their user interface. After the comments have been created, the user name displayed in the status bar is fixed and will not be affected when you change your User name setting back to your own name.

Remember that cell comments can be rich-text formatted. This means you can use formatting such as bold and italic fonts within the comment text as well as multiple fonts. Rich-text formatting enables you to create some very sophisticated help messages. Figure 4-18 shows a rich-text-formatted cell comment from a real-world worksheet user interface.

Figure 4-18. A Rich-Text-Formatted Cell Comment

Using Shapes

The ability to use shapes (objects drawn using the various options on the Drawing or Forms toolbars) on an Excel worksheet is a very powerful user interface technique. Shapes are located in a special drawing layer that exists above the cells on a worksheet, so shapes cover (and obscure) worksheet cells. Shapes are also connected to the underlying worksheet through their properties, which allow them to do the following:

Move and size with the worksheet cells they cover

Move but don't size with the worksheet cells they cover

Don't move or size with the worksheet cells they cover

Almost all shapes can contain text. A shape's text can either be manually entered or it can be linked dynamically to a specific cell on a worksheet by selecting the shape and entering the address of that cell as a formula in the formula bar. As you can imagine, the ability to assign formulas to shapes opens up a wide array of options for creating dynamic user interfaces. Shapes can also be given a macro assignment that will cause them to execute the specified macro whenever the user clicks them. Just right-click over the shape and choose Assign Macro from the shortcut menu. Figure 4-19 shows an excellent example of how shapes can be used to create a custom toolbar-like area across the top of a worksheet user interface.

Figure 4-19. A Custom On-Sheet Toolbar Created with Shapes

[View full size image]