Changing Boolean settings

Like a light switch, a Boolean setting is either on or off. For example, you might want to create a macro that turns the worksheet row and column headings on and off. With the headings turned on, Excel generates the following code if you record your actions while accessing the Options dialog box:

ActiveWindow.DisplayHeadings = False

On the other hand, if the headings are turned off when you record the macro, Excel generates the following code:

ActiveWindow.DisplayHeadings = True

This may lead you to suspect that you need two macros: one to turn on the headings and one to turn them off. Not true. The following procedure uses the Not operator to effectively toggle the heading display from True to False and from False to True:

Sub ToggleHeadings()

If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub ActiveWindow.DisplayHeadings = Not _ ActiveWindow.DisplayHeadings

End Sub

The first statement ensures that the active sheet is a worksheet. (Chart sheets don't have headings.) If a worksheet is not active, the procedure ends. You can use this technique with any settings that have Boolean (True or False) values.

0 0

Post a comment