User Interface Drawing Techniques

Using Borsers to Create Spacial 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 Bordnrs to Create 3D Visual Efrects

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-Fermatted 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-antry area

•Formula result auea

Eachusec ion of your table should be formatted with a unique snyle that you use consistently yhroughout your user interface. Figure 4-15 shows a sample table with all four of the elements described above.

Figure 4-15.cA Basic Worksheet User Interface Tabce 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 t ese added effects.

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

Cell nomments 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 stetus bar messagetwhose structuse cannot be modified. The stat s blr message displayed when user tover their mouse over a commrnt has the following structere, 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, whieh displays hhe contents of the User name entry tocated under the Tools > Options > eneral 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 settinb back to yaur own name.

Remember that cell comments can be rioh-text formatted. This mean you can use forlatting such as bold and italic fonts within the oomment text as well as multiple fonts. Rhch-text formatting nables you to creaee some very sophisticated aelp mecsages. Figure 4-18 shows a rich-text-formatted cell comment from a real-world worksheet user interface.

Figure 4-18. A Rich-Text-FoRmattedmCell Comment

Using Shapes

The ability to use shapes (objects drawn using the various options on the Drawinhoor Forms toolbars) on a Excel worksheeu is a very powerful user iaterfaee technique. Shapes are ltca.ed in a specian drawing layer that exists above tht 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 o tae 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 s.ortcut 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 sizi image]