Excel tips: how to create macros that toggle

Question

I use macros and have written one that turns on a setting and another that turns it off. Is there a way to have one macro that switches between off and on?

Answer

In many cases you can create a single macro that will switch settings between off and on. It is usually called a toggle, and it acts like many of Excel’s built-in formats.

To demonstrate the technique we will examine four useful macros. The first changes a format, the second changes a sheet display setting, and the last two amend print settings. (When referring to Excel options, I will use the US spelling that matches the spelling used by Excel) 

This format is used when you want to centre an entry across more than one column. Most people use the Merge and Center format, but it creates a few problems that can be frustrating.

Center Across Selection has two advantages over the Merge and Center option. It doesn’t stop you from copying and pasting, and it doesn’t crash macros.

Its downside is that it doesn’t have an icon that you can add to the Quick Access Toolbar (the customisable toolbar that is situated above or below the ribbon). This makes it an ideal feature to use in a macro. It’s also a good example of a toggle macro.

The macro in Figure 1 will apply or remove the Center Across Selection format.

This macro works on the range selected before the macro is run because it uses the Selection keyword. The On Error Resume Next command stops the macro from crashing if it is run on a protected worksheet.

The If statement checks to see if the HorizontalAlignment setting is set to xlCenterAcrossSelection. If it is, it changes the format to xlGeneral, which is Excel’s standard format setting. Otherwise it will change the setting to xlCenterAcrossSelection, which applies the format.

You can learn these settings by recording macros that change a setting or apply a format, and then reviewing the code created in the recorded macro. To see the code after recording a macro, press Alt + F8 and select the macro on the left and click the Edit button on the right.

An option that is selected by a checkbox is most likely controlled via True and False settings. In that case, it’s even easier to create a toggle macro. Take, for example, the “Show a zero in cells that have zero value” setting in the Advanced section of the Excel Options dialog, as shown in Figure 2.

Select the Advanced section in the Excel Options dialog, then scroll down about two-thirds of the way to the “Show a zero in cells that have a zero value” option.

When unticked, this option will hide zero values on the whole sheet and stop them from displaying and printing. I have worked for a few managers that have disliked zeroes displaying in their reports. The macro that toggles this option off and on uses a single line command, see Figure 3.

This works on whatever sheet is active when the macro is run. This takes advantage of the Not keyword. The Not keyword will switch True to False and False to True.

That means you can use the current setting with the Not keyword to switch the setting to its opposite, as shown in Figure 3. An If statement is not required. You can only use the Not keyword with entries that return True and False.

This programming technique is efficient, but it does confuse people because the setting is used on both sides of the equation. Excel is aware of all the settings in place at the time the macro is run. By using the setting itself on the right side of the equation you extract the current setting.

The Not keyword switches the current setting to its opposite, and that is used to modify the setting. Following are two more examples that both affect print settings.

Figure 4 shows a macro that will switch the page orientation between Landscape and Portrait for the active sheet. It uses the same sort of structure as the macro shown in Figure 1.

Figure 5 shows another example of using the Not keyword. This macro hides or displays the dotted lines that show page breaks on the sheet that is active when the macro is run. This setting is directly above the one highlighted in Figure 2.

Using these techniques lets you create a single macro that performs two processes, reducing the number of macros you need to generate. The companion video on intheblack.com will demonstrate how to run macros like the ones above, off icons on the ribbon.

Excel tip

Repeating an action: The F4 function key in Excel repeats the last action preformed. This can also be used after running a macro. If you have just the CentreAcrossSelection macro from Figure 1 to format one range, you can select another range and press the F4 to repeat applying the format.

Pressing F4 after using a toggle macro will revert the setting back to it's original setting. The F4 key also repeats action in Word and PowerPoint.