In the latest post, I showed how to assign a macro to a Forms menu control or other shape on a sheet. Macros can also be run from the ActiveX controls on the Control Toolbox.

We’ll use this simple macro for this example.

Sub HelloWorld()
  MsgBox "Hello, World! ", vbExclamation
End Sub
 

When it is run, we see a simple message.

Control Toolbox ActiveX Controls

Forms toolbar controls have been part of Excel since (I think) Excel 4. They are stable and easy to use.  And they are old-fashioned. The Control Toolbox has newer ActiveX controls, introduced with Excel 97. These are fancier controls, with more formatting options, and somewhat more dynamic behavior. They sometimes are blamed for erratic behavior, and seasoned Excel developers generally prefer the Forms controls. However, these controls can provide some slick effects.

To use Control Toolbox controls, you need to make the Control Toolbox visible (actually it’s a toolbar). Use the same techniques as shown to make the Forms toolbar visible, or click the Control Toolbox button on the Visual Basic toolbar.

Click the Command Button button, and drag a rectangle in the worksheet where you want the button. No Assign Macro dialog pops up, however, and when you right click on the button, the context menu has no Assign Macro element either.

ActiveX controls work a bit differently. When they are clicked, or interacted with in other ways (e.g., when scrollbars are scrolled, when items in a combobox are selected, etc.), they raise events, which VBA can respond to. Instead of the missing Assign Macro item, click on View Code in the context menu. This inserts an event procedure in the code module behind the worksheet containing the button.

You can write code within this event procedure that runs whenever CommandButton1 is clicked. Note the two dropdowns at the top of the code module. The left dropdown shows CommandButton1, and if there were other ActiveX controls on the worksheet, they would also be listed when this dropdown is pulled down. The right dropdown lists the events which the selected control in the left dropdown responds to.

It is the wealth of these events which make ActiveX controls more powerful, and also perhaps a little flaky. But no rants now, this is a family blog.

To run the HelloWorld macro when the command button is clicked, call it from within the CommandButton1_Click event procedure. To do that, simply type the name of the macro, HelloWorld, within the event procedure.

Related Posts:

• Forms Controls and ActiveX Controls in Excel
• How To: Assign a Macro to a Button or Shape
• How To: Assign a Macro to a Toolbar or Menu
• Sample Parallel Coordinate Chart
• How To: Record Your Own Macro
• Dynamic Chart using Pivot Table and VBA
• How To: Use Someone Else’s Macro
• Highlight a Series with a Click or a Mouse Over
• Chart Event Class Module to Highlight a Series
• Build an Excel Add-In 4 – Create the Dialog