How to Use a VBA Macro to Change Excel 2016 Settings
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.