How to Run VBA Code from an Excel Worksheet

1.        What are Controls?

First, some terminology. If you wanted to run a macro from a worksheet, you could create a button on the worksheet for the user to click. Such a button is a Control. There are many types of controls, which we will use in a later post. Apart from Command Buttons to run a macro, there are Option Buttons.  Often called radio buttons, these let the user make one of several choices. There are Check Boxes for multiple choices. We may also want to use drop-down lists, combo boxes, scrollbars and other features.

For historical reasons, Excel offers us two types of controls, Form Controls and ActiveX Controls. We will go into the differences in a later post. In this post, we will focus on Command Buttons. You will see other controls in passing.

2.        Creating a Microsoft Forms Command Button

You can put a command button (or another control) on your worksheet and set it to run a macro when you click on it.  To do this:

• Show the Developer ribbon.  If it is not visible, click File > Options > Customize Ribbon, and then tick Developer.
• On the Developer ribbon, in the Controls group, click Insert to get the drop-down list above.  You can find out what any control is by hovering the mouse pointer over the relevant button.
• Click the “Button” Button, top left in the Form Controls section:
• Drag on the worksheet where you would like to draw the button.
• Fill in the dialog box that appears when you let go of the mouse.  Either choose an existing macro, or name and record a new one.
• When you have finished the above, notice that the button is Selected. It has selection handles and a line border.  While it is selected, you can type a new caption on it, resize by dragging a handle, or move it by dragging the border.

When you click on the worksheet, the button is unselected.  Then, clicking on it runs the macro.

To select the button again, Ctrl+Click on the button.

To get back into allocating a macro, select the button.  Right-click on the button: From the pop-up menu choose Assign Macro.

3.        Creating a Visual Basic ActiveX Command Button

There is another kind of Command Button that you can put on your worksheet and set to run a macro.  This is in the ActiveX controls.  To do this:

• Show the Developer ribbon.  In the Controls group, click Insert to get the drop-down list above.
• Click the “Command Button” Button, top left in the ActiveX Controls section:
• Drag on the worksheet where you would like to draw the button.
• When you have done the above, notice that the CommandButton is Selected (has selection handles and a line border).  While it is selected, you can resize it by dragging a handle, or move it by dragging the border.
• Notice also on the Developer ribbon, that Excel goes into Design Mode: The Design Mode appears “lit up”.
• Right-click the button. On the pop-up menu, click “Properties” :

• Fill in the Property “(Name)” with a suitable name for your button, starting with “cmd”, e.g. cmdTest.
• Fill in the Property “Caption” with the text that you want to appear on the button.

4.        Connecting an ActiveX Button to Visual Basic Code

Notice that there is no obvious way to connect the button to an existing macro.  To show why, right-click the button.

• On the pop-up menu, click “View Code”.
• The Visual Basic Editor appears, on the Sheet (Project pane), showing:

Private Sub cmdTest_Click()

End Sub

• ActiveX Controls automatically run Sub procedures named in the format “Control Name” + underscore (_) + event (Click, DblClick, GotFocus, etc.).  Insert the code you want the button to run, between the Sub and End Sub statements. To get the button to run a macro located elsewhere, e.g. Test1, just put its name inside the code:

Private Sub cmdTest_Click()
  Test1
End Sub

To go out of Design Mode, click the Design Mode button so that it is no longer “Lit up”.  Then, clicking on the button runs its macro code.

Another way to make the code connection is from the Visual Basic Editor:

5.        Attaching Visual Basic Code to an ActiveX Button

Assume that we have created the button and named it.  To attach a macro to it from the Visual Basic Editor, do this:

• Open the Visual Basic Editor (Alt+F11).
• In the Project Explorer, locate the VBA Project, and the sheet containing the button.  Double-click it to open the code window.
• In the Code Window, pull down the left-hand list [it will say “(General)”] and choose the name of your button from the list.
• This will create Sub buttonname_Click (where buttonname is the name you gave the button.
• In this Sub, enter the Visual Basic code you want the button to run, e.g.:

• Click Debug > Compile VBA Project. If there are any errors, correct them and recompile, then swap to the worksheet and test your button.

Tip: If you would like to centralise your code, or you have code you want to run from similar ActiveX buttons on several worksheets, put the Sub in a Module.  Then, in each button’s “_Click” code, use

Call SubInModule

Trap: There is nothing stopping you from typing Sub buttonname_Click Enter, instead of using the pull-down list.  But, if you misspell the name of your sub, the code won’t connect to the button, and won’t run!  If you omit the “_Click”, when you compile you get the confusing error message:

6.        Assigning a Macro to a Quick Access Toolbar Button

The Quick Access Toolbar normally sits at the extreme top left of the Excel window, in the Title Bar.  To assign a macro to a button there, click the rightmost button on the toolbar and, near the bottom of the drop-down menu, choose More Commands…

• In the right-hand list (“Customize Quick Access Toolbar”), decide if you want the button to be available to all documents, or just this file.
• In the right-hand list, click on the button after which you want to insert the macro.
• In the left-hand list (“Choose commands from”), select Macros.
• Locate your Macro in the left-hand list, and click on it.
• Click the Add >> button.
• To change the button icon or Tooltip text (Display name), click Modify…

The advantage of Toolbar Buttons over Command buttons on a Worksheet is that Toolbar Buttons are always available. 

If you click a Toolbar button and the macro it calls does not exist in an open worksheet, the program will load the necessary worksheet.  You may want to hide your Macros file (Window > Hide) so that the loading is not disruptive.  You may also put it into your StartUp folder so that your macros are always loaded when Excel starts.  Alternatively, put your macros into Personal.xlsb so that the job is done for you!

7.        Assigning a Macro to a Button on the Ribbon

While we do not recommend it, you can in fact assign a macro to a button almost anywhere on any ribbon.

This is also in the Excel Options dialog box as discussed above.  You can get there using the Quick Access Toolbar’s rightmost button > More Commands… or File > Options.  Directly above the Quick Access Toolbar tab on the left, is Customize Ribbon.  Click it.

It looks and works much like the Quick Access Toolbar tab above.  The main difference is that, in the right-hand list, you can choose a Tab (ribbon) (or create a New Tab) on which to place a New Group.  To this, you can Add>> your macro button.

The Rename button lets you set the name (used as the ToolTip) and choose an image for the button.  If a Tab or Group is selected, the Rename button lets you change its name.

In this post, we looked at command buttons on the Worksheet, the Quick Access Toolbar and the Ribbon.  What about the other Controls: The Combo Box, List Box, or Check Box?  How about Option Buttons, the Scroll Bar, Spinner, Toggle button, etc.?  They will be the subject of our next two posts: Form Controls vs. ActiveX Controls, Other Form Controls and Other ActiveX Controls.