Page 256 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

Formatting Worksheet Elements

Now that you have the Excel color system well in hand, you can get to work changing the
appearance of the elements of your worksheet elements. There are two elements you can
change at the window and worksheet level: gridlines and sheet tabs. In the default Excel
workbook configuration, the gridlines are a medium gray. If you want to change that color to
better fit your design, you can do so by setting the color to a custom RGB value or a color
constant using the ActiveWindow.GridlineColor property or, if you want to assign a color
from the Excel color palette, the ActiveWindow.GridlineColorIndex property. A benefit of using
the GridlineColorIndex property is that you will have the ability to change the gridlines back
to the default color by setting the property’s value to the VBA constant xlColorIndexAutomatic.
As an example, the following procedure changes the gridlines to blue, then to white (which
makes the gridlines invisible), and then changes them back to the automatic color:

Sub CycleGridlines()
MsgBox (“Changing the gridline color to blue.”)
ActiveWindow.GridlineColorIndex = 5
MsgBox (“Changing the gridline color to white.”)
ActiveWindow.GridlineColor = RGB (255, 255, 255)
MsgBox (“Changing the gridline color back to the default color.”)
ActiveWindow.GridlineColorIndex = xlColorIndexAutomatic
MsgBox (“Ending the procedure.”)
End Sub

Note The GridlineColorIndex property Help topic displays the default colors of the Excel
color palette.

The other worksheet-level element you can change is the sheet tab. The sheet tabs appear on
the tab bar at the bottom left of the Excel window. The tabs are normally white with black let­
Chapter 10
tering when active and a neutral gray color when inactive, but you can highlight one or more of
them by changing their color using either the Worksheet.Tab.ColorIndex or Worksheet.Tab.Color
property. For example, if you wanted to change the tab of any worksheet where a user
changes the existing data, you could do so by placing the following event procedure in the
code module associated with each worksheet you want to monitor:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
ActiveWorkbook.ActiveSheet.Tab.ColorIndex = 5
End Sub

For more information on Excel events in general, and what does or does not trigger the
Worksheet_Change event in particular, see Chapter 12, “Understanding and Using Events.”

230
Part 4: Advanced VBA