Changing Excel Settings

Some of the most useful macros are simple procedures that change one or more of Excel's settings. For example, simply changing the recalculation mode from automatic to manual requires numerous steps. You can save yourself some keystrokes and menu choices (not to mention time) by creating a macro that automates this task.

This section presents two examples that show you how to change settings in Excel. You can apply the general principles demonstrated by these examples to other operations that change settings.

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 page break display on and off. After you print or preview a worksheet, Excel displays dashed lines to indicate the page breaks. Some people (author included) find this annoying. Unfortunately, the only way to get rid of the page break display is to open the Excel Options dialog box, click the Advanced tab, and scroll down until you find the Show Page Breaks check box. If you turn on the macro recorder when you change that option, Excel generates the following code:

ActiveSheet.DisplayPageBreaks = False

On the other hand, if page breaks are not visible when you record the macro, Excel generates the following code:

ActiveSheet.DisplayPageBreaks = True

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

Sub TogglePageBreaks()

On Error Resume Next

ActiveSheet.DisplayPageBreaks = Not _ ActiveSheet.DisplayPageBreaks End Sub

The first statement ignores an error that occurs if the active sheet is a chart sheet. (Chart sheets don't display page breaks.)

You can use this technique with any settings that have Boolean (True or False) values.

Changing non-Boolean settings

Use a Select Case structure for non-Boolean settings. This example toggles the calculation mode between manual and automatic and displays a message indicating the current mode:

Sub

ToggleCalcMode()

Select Case Application.Calculation

Case xlManual

Application.Calculation =

xlCalculationAutomatic

MsgBox "Automatic Calculation Mode"

Case xlAutomatic

Application.Calculation = xlCalculationManual

MsgBox "Manual Calculation Mode"

End Select

End

Sub

You can adapt this technique for changing other non-Boolean settings.

You can adapt this technique for changing other non-Boolean settings.

0 0

Post a comment