There’s a previous post that describes various ways to run a VBA macro, with a portion of that post discussing how to create a button to execute a macro. That post describes the process of creating a button for the classic user-interface but the process is different for the ribbon user-interface. This post describes the differences when using the ribbon.
To create a button to execute a VBA macro in the ribbon user-interface you use the Customize User Commands command which you access by right-clicking anywhere on the ribbon, as shown below.
The command displays the dialog shown below. To choose a VBA macro select “Macros” in the drop-down in the upper-left corner of the dialog. The list of commands will now show all of the VBA macros in the application project. Select the desired macro and then click the “Add >>” button in the middle of the dialog. You can continue to select and add other macros and then click OK when finished.
Buttons for the selected macros are added to a new panel, as shown below. The “User Commands” panel is added to the currently active ribbon tab, so by choosing which tab to add the macros to you have some control over when the command is available. If you need it in more than one tab, you’ll need to go through the process for each tab.
As explained in the other post, you can assign your own icons, as I’ve done in this example. This is done in exactly the same way as described before, by creating a bmp file, saving it in the same directory as the .ivb file, and naming it correctly. One thing to be aware of is that you don’t have control over whether the button will be displayed as a large or small button; it’s always small. Because of that the bitmap you create should be 16×16 pixels and should be named “ModuleName.MacroName.Small.bmp”. Unfortunately, you don’t have the option of displaying text with the button. Having a recognizable icon will help with this and the tooltip for the button displays the name of the macro.
Leave a Reply