Understanding Document Themes | Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

A significant new feature in Excel 2007 is document themes. With a single mouse click, the user can change the entire look of a document. A document theme consists of three components : colors, fonts, and effects (for graphic objects). The rationale for using themes is that they may help users produce better-looking and more consistent documents. A theme applies to the entire workbook, not just the active worksheet.

About document themes

Microsoft Office 2007 ships with 20 document themes, and additional themes can be added. The user interface Ribbon includes several style galleries (for example, the Chart Styles gallery). The styles available in these galleries vary depending on which theme is assigned to the document. And, if you apply a different theme to the document, the document changes to reflect the new theme's colors, fonts, and effects.

CD-ROM

If you haven't explored document themes, open the workbook named document theme demo.xlsx found on the companion CD-ROM. This workbook contains a range that shows each theme color , two shapes , text (using the headings and body fonts), and a chart. Choose Page Layout Themes Themes Gallery to see how the worksheet changes with each theme.

Users can also mix and match theme elements. For example, it's possible to use the colors from one theme, the fonts from another theme, and the effects from yet a different theme. In addition, the user can create a new color set or a new font set. These customized themes can be saved and then applied to other workbooks.

Note

The concept of document themes is based on the notion that users will apply little, if any, non-theme formatting to the document. If the user applies colors or fonts that aren't part of the current theme, this formatting will not be modified if a new theme is applied to the document. Therefore, it's still very easy to create an ugly document with mismatched colors and too many different fonts.

Understanding document theme colors

When a user applies a color to a cell or object, the color is selected from a control like the one shown in Figure 30-4. The control displays the 60 theme colors (10 columns by 6 rows) plus 10 additional standard colors. Clicking the More Colors option displays the Color dialog box, in which the user can specify any of the 16,777,216 available colors.

Figure 30-4: A color-selection control.

The 60 theme colors are identified by pop-up ToolTips. For example, the color in the second row of the sixth column is known as " Accent 2, Tint 20%." Table 30-2 shows the names of all 60 theme colors. Examine the table, and you see that there isn't much of a pattern - although the Accent colors are consistent in terms of the tint and shade variations. Also, note that some color variations are known as a tint and others are known as a shade .

Table 30-2: THEME COLOR NAMES
Open table as spreadsheet
Row/Column	1	2	3	4	5	6	7	8	9	10
1	Text/	Text/	Text/	Text/	Accent	Accent	Accent	Accent	Accent	Accent
	Background	Background	Background	Background	1	2	3	4	5	6
	1	2	3	4
2	Shade	Tint	Shade	Tint	Tint	Tint	Tint	Tint	Tint	Tint
	95%	95%	90%	20%	20%	20%	20%	20%	20%	20%
3	Shade	Tint	Shade	Tint	Tint	Tint	Tint	Tint	Tint	Tint
	85%	65%	75%	40%	40%	40%	40%	40%	40%	40%
4	Shade	Tint	Shade	Tint	Tint	Tint	Tint	Tint	Tint	Tint
	75%	75%	50%	60%	60%	60%	60%	60%	60%	60%
5	Shade	Tint	Shade	Shade	Shade	Shade	Shade	Shade	Shade	Shade
	65%	85%	25%	75%	75%	75%	75%	75%	75%	75%
6	Shade	Tint	Shade	Shade	Shade	Shade	Shade	Shade	Shade	Shade
	50%	95%	10%	50%	50%	50%	50%	50%	50%	50%

Keep in mind that these color names remain the same, even if a different document theme is applied. The document theme colors actually consist of the 10 colors displayed in the top row (four text/background colors and six accent colors), and each of these 10 colors has five tint/shade variations.

Note	If you select Page Layout Themes Colors Create New Theme Colors, you see that a theme has two additional colors: Hyperlink and Followed Hyperlink. These are the colors applied when a hyperlink is created, and they are not shown in the color selector control.

You may find it enlightening to record a macro while you change the fill color and text color of a range. Following is a macro that I recorded when a range was selected. For the fill color, I chose "Accent 2, Shade 75%," and for the text color, I chose "Text/Background 3, Shade 90%."

 Sub Macro1()     With Selection.Interior         .Pattern = xlSolid         .PatternColorIndex = 56         .ThemeColor = 6         .TintAndShade = -0.249977111117893         .PatternTintAndShade = 0     End With     With Selection.Font         .ThemeColor = 3         .TintAndShade = -9.99786370433668E-02     End With End Sub

First of all, you can safely ignore the three pattern- related properties ( Pattern , PatternColorIndex , and PatternTintAndShade ). These properties refer to the ugly, old-fashioned (but still supported) cell patterns, which you can specify in the Fill tab of the Format Cells dialog box. These properties simply maintain any existing pattern that may exist in the range.

The recorded macro, after I delete the three pattern-related properties, is

 Sub Macro1()     With Selection.Interior         .ThemeColor = 6         .TintAndShade = -0.249977111117893     End With     With Selection.Font         .ThemeColor = 3         .TintAndShade = -9.99786370433668E-02     End With End Sub

As you can see, each color is specified in terms of a ThemeColor property and a TintAndShade property. The ThemeColor property is easy enough to decipher. It's simply the column number of the 10-x-6 theme color table. But what about the TintAndShade property?

The TintAndShade property can have a value between “1 and +1. A value of “1 results in black, and a value of +1 results in white. A TintAndShade property value of 0 gives the pure color. In other words, as the TintAndShade value goes negative, the color gets increasingly darker until it's pure black. As the TintAndShade value goes positive, the color gets increasingly lighter until it's pure white.

To arrive at the TintAndShade property value that corresponds to a particular theme color variation, look at Table 30-2.

If the color variation is expressed as a Tint, the TintAndShade property value is 1 minus the percent value (a positive value, making the variation lighter than the original color).
If the color variation is expressed as a Shade, the TintAndShade property value is the percent value minus 1 (a negative value, making the variation darker than the original value).

CD-ROM

For a demonstration of how the TintAndShade property changes a color, open the tintandshade demo.xlsm workbook on the companion CD-ROM (see Figure 30-5). Specify a starting color, and the macro displays that color with 50 levels of the TintAndShade property values, ranging from “1 to +1. It also displays the decimal color value and the red, green, and blue components of the color (which are displayed in a chart).

Figure 30-5: This workbook demonstrates how the TintAndShade property affects a color.

Displaying all theme colors

Using the information in Table 30-1, I wrote a macro that displays all 60 theme color variations in a range of cells.

 Sub ShowThemeColors()   Dim r As Long, c As Long   For r = 1 To 6     For c = 1 To 10         With Cells(r, c).Interior         .ThemeColor = c         Select Case c             Case 1 'Text/Background 1             Select Case r                 Case 1: .TintAndShade = 0                 Case 2: .TintAndShade = -0.05                 Case 3: .TintAndShade = -0.15                 Case 4: .TintAndShade = -0.25                 Case 5: .TintAndShade = -0.35                 Case 6: .TintAndShade = -0.5             End Select         Case 2 'Text/Background 2             Select Case r                 Case 1: .TintAndShade = 0                 Case 2: .TintAndShade = 0.5                 Case 3: .TintAndShade = 0.35                 Case 4: .TintAndShade = 0.25                 Case 5: .TintAndShade = 0.15                 Case 6: .TintAndShade = 0.05             End Select         Case 3 'Text/Background 3              Select Case r                  Case 1: .TintAndShade = 0                  Case 2: .TintAndShade = -0.1                  Case 3: .TintAndShade = -0.25                  Case 4: .TintAndShade = -0.5                  Case 5: .TintAndShade = -0.75                  Case 6: .TintAndShade = -0.9              End Select          Case Else 'Text/Background 4, and Accent 1-6              Select Case r                  Case 1: .TintAndShade = 0                  Case 2: .TintAndShade = 0.8                  Case 3: .TintAndShade = 0.6                  Case 4: .TintAndShade = 0.4                  Case 5: .TintAndShade = -0.25                  Case 6: .TintAndShade = -0.5               End Select          End Select         Cells(r, c) = .TintAndShade          End With      Next c    Next r End Sub

Figure 30-6 shows the result of executing the ShowThemeColors procedure (it looks better in color). If you change to a different document theme, the colors will be updated to reflect those in the new theme.

Figure 30-6: A VBA macro generated these theme colors.

CD-ROM

This example, named generate theme colors.xlsm , is available on the companion CD-ROM.

So far in this chapter, I've described how to change the fill color of a range by setting the Color property of the Interior object. As I noted, using the VBA RGB function makes this easier. These two statements demonstrate how to change the fill color of a range (they both have the same result):

 Range("A1:F24").Interior.Color = 5913728 Range("A1:F24").Interior.Color = RGB(128, 60, 90)

What if you'd like your code to change the background color of a range to a specific theme color, such as the color in the third row of the sixth column (the color identified as "Accent 2, Tint 40%")?

Unfortunately, the Excel 2007 designers seemed to have forgotten to include a direct way to specify a theme color using this type of indexing. You might think the ColorIndex property would do the job, but it doesn't. The ColorIndex property refers to colors in the (pre “Excel 2007) 56-color palette.

In actual practice, this omission is not a serious problem. When setting a color, the important property is the ThemeColor property, which ranges from 1 to 10. Your code can assign a value to the TintAndShade property to vary that color (a negative value for a darker variation, a positive value for a lighter variation). If the user applies a different document theme, the color still changes in a relative manner.