Microsoft Office Tutorials and References
In Depth Information
A workbook with this example is available on the companion CD-ROM in a file named
synchronize sheets.xlsm .
The examples in this section illustrate common VBA techniques that you might be able to adapt
to your own projects.
Toggling a Boolean property
A Boolean property is one that is either True or False . The easiest way to toggle a Boolean
property is to use the Not operator, as shown in the following example, which toggles the
WrapText property of a selection.
‘ Toggles text wrap alignment for selected cells
If TypeName(Selection) = “Range” Then
Selection.WrapText = Not ActiveCell.WrapText
You can modify this procedure to toggle other Boolean properties.
Note that the active cell is used as the basis for toggling. When a range is selected and the
property values in the cells are inconsistent (for example, some cells are bold, and others are not), it’s
considered mixed, and Excel uses the active cell to determine how to toggle. If the active cell is
bold, for example, all cells in the selection are made not bold when you click the Bold button. this
simple procedure mimics the way Excel works, which is usually the best practice.
Note also that this procedure uses the TypeName function to check whether the selection is a
range. If the selection isn’t a range, nothing happens.
You can use the Not operator to toggle many other properties. For example, to toggle the
display of row and column borders in a worksheet, use the following code:
ActiveWindow.DisplayHeadings = Not ActiveWindow.DisplayHeadings
To toggle the display of gridlines in the active worksheet, use the following code:
ActiveWindow.DisplayGridlines = Not ActiveWindow.DisplayGridlines