How to Use a VBA Macro to Change Excel 2016 Settings

By John Walkenbach

Some of the most useful macros are simple procedures that change one or more of Excel’s settings. For example, if you find yourself making frequent trips to the Excel Options dialog box to change a setting, that’s a good candidate for a simple time-saving macro.

Here are 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 find these dashed lines very 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 conclude 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, which turns True to False and False to True. Executing the TogglePageBreaks procedure is a simple way to 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 tells Excel to ignore any errors. For example, a chart sheet doesn’t display page breaks, so if you execute the macro when a chart sheet is active, you won’t see an error message.

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

Changing non-Boolean settings

You can 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.