Executing a Ribbon Command with a Macro
This tip describes how to write VBA code that mimics clicking a command on the Excel Ribbon.
For example, you can write a macro that executes the Home➜Alignment➜Align Text Left
command.
To execute a Ribbon command, you must know the command’s name. One way to find a control’s name is to use the Customize Ribbon tab of the Excel Options dialog box.
Choose File➜Options and click the Customize Ribbon tab. Then locate the command in the list on the left and hover your mouse over the command. A ToolTip displays the command’s name (in parentheses).
In Figure 211-1, for example, the ToolTip indicates that the command name for Home➜
Alignment➜Align Text Left is AlignLeft. The following VBA macro, when executed, has the same effect as choosing the Home➜Alignment➜Align Text Left command:
Sub ExecuteAlignLeft( )
CommandBars.ExecuteMso “AlignLeft”
End Sub
Figure 211-1: Using ToolTips to determine command names.
You can download a workbook from the Microsoft site that lists all Excel Ribbon command names — more than 1,700 of them. Try searching the Web for Office 2010
Ribbon commands.
Here’s another example that displays the Font tab of the Format Cells dialog box:
Sub ShowFontTab()
CommandBars.ExecuteMso “FormatCellsFontDialog”
End Sub
Attempting to execute a command in an incorrect context generates an error. For example, this statement executes the command that displays the Insert Function dialog box: CommandBars.ExecuteMso “FunctionWizard” If you execute this statement when something other than a range is selected (for example, a chart), you get an error. Here’s a procedure that checks for such an error and displays a friendly message:
Sub ShowInsertFunction( )
On Error Resume Next CommandBars.ExecuteMso “FunctionWizard”
If Err.Number <> 0 Then
MsgBox “That command is not appropriate.”, vbInformation
End If
End Sub
