How to Use VBA to Display Excel 2016’s Built-in Dialog Boxes - dummies

How to Use VBA to Display Excel 2016’s Built-in Dialog Boxes

By John Walkenbach

Being a VBA programmer can be overwhelming at times. One way to look at VBA is that it’s a tool that lets you mimic Excel commands. For example, consider this VBA statement:

Range(“A1:A12”).Name = “MonthNames”

Executing this VBA statement has the same effect as choosing Formulas  →  Defined Names   →  Define Name to display the New Name dialog box, typing MonthNames in the Name box and A1:A12 in the Refers to box, and clicking OK.

When you execute the VBA statement, the New Name dialog box does not appear. This is almost always what you want to happen; you don’t want dialog boxes flashing across the screen while your macro executes.

In some cases, however, you may want your code to display one of Excel’s many built-in dialog boxes and let the user make the choices in the dialog box. You can do this by using VBA to execute a Ribbon command. Here’s an example that displays the New Name dialog box. The address in the Refers To box represents the range that’s selected when the command is executed.

Displaying one of Excel’s dialog boxes by using VBA.
Displaying one of Excel’s dialog boxes by using VBA.
Application.CommandBars.ExecuteMso “NameDefine”

Your VBA code can’t get any information from the dialog box. For example, if you execute the code to display the New Name dialog box, your code can’t get the name entered by the user or the range that’s being named.

The ExecuteMso is a method of the CommandBars object and accepts one argument: an idMso parameter that represents a Ribbon control. Unfortunately, these parameters are not listed in the Help system. And because the Ribbon hasn’t been around forever, code that uses the ExecuteMso method is not compatible with versions before Excel 2007.

Here’s another example of using the ExecuteMso method. This statement, when executed, displays the Font tab of the Format Cells dialog box:

Application.CommandBars.ExecuteMso 
“FormatCellsFontDialog”

If you try to display a built-in dialog box in an incorrect context, Excel displays an error message. For example, here’s a statement that displays the Format Number dialog box:

Application.CommandBars.ExecuteMso “NumberFormatsDialog”

If you execute this statement when it’s not appropriate (for example, a Shape is selected), Excel displays an error message because that dialog box is appropriate only for worksheet cells.

Excel has thousands of commands. How can you find the name of the one you need? One way is to use the Customize Ribbon tab of the Excel Options dialog box. The quick way to get there is to right-click any Ribbon control and choose Customize the Ribbon from the shortcut menu.

Virtually every command available in Excel is listed in the left panel. Find the command you need and hover your mouse over it, and you see its secret command name in the tooltip (it’s the part in parentheses).

Using the Customize Ribbon tab to identify a command name.
Using the Customize Ribbon tab to identify a command name.