Return to VBA Code Examples

Excel Add VBA Button

This tutorial will demonstrate how to add a macro button in Excel VBA.

Once we have created macros in Excel, it is sometimes useful to create a button in the Excel worksheet to run the macro.

Also, as we  use VBA to create UserForms in Excel, we need to be able to create buttons on these UserForms to perform the OK and Cancel operations.  This tutorial will demonstrate how to add a macro button to a VBA worksheet, how to add a macro to a shape in VBA (as opposed to a button), and how to add a button onto a User Form in the VBE Editor.

Adding a Macro Button to an Excel Worksheet

Once we have created a macro in Excel VBA, we can create a button on the worksheet to run the macro.

In the Ribbon, select the Developer tab if it is visible. This tab is switched off by default so you will need to switch it on to see the tab on the ribbon.

If the Developer tab is not visible on the ribbon, click on the File tab in the Ribbon, and go down to Options. In the Customize Ribbon options, tick the Developer check box.

vba vbe developer

Click OK.

The Developer tab will appear on the main ribbon.

vba vbe vbescreen

There are 2 types of buttons we can add to an Excel sheet – a Form Control button and an ActiveX Control Button.

Adding a Form Control Button

In the Ribbon, select Developer > Insert > Form Controls > Button.

VBA Buttons FormControl

Click and drag in the worksheet to create a button.  As soon as you release the mouse button, the assign macro dialog box will appear.

VBA Buttons AssignMacro

Scroll down to 1) select the macro you wish to assign to the button, and then, 2) click OK.

VBA Buttons AssignMacro OK

 

Right click on the button, and select Edit Text to change the text on the button.

VBA Buttons Edit Text

Type an appropriate name for the button and then click off the button.

Click the button to run the macro.

VBA Buttons ClickButton

To change the format of the button, right click on the button and select Format Control.

VBA Buttons Format

Select the options to format, and click OK.

VBA_Buttons FormControl Format

Adding an ActiveX Button

The ActiveX button is similar to a Form Control Button but instead of assigning an existing macro to the button, we need to write code behind the Click Event of the button. It also has a lot more Properties than the Form Control Button making it a much more flexible control.

In the Ribbon, select Developer > Insert > ActiveX Controls > Command button.

VBA Buttons ActiveX Button

Click and drag in your worksheet to create the button.   The button will appear as an Embedded Command Button.

VBA Buttons ActiveX EmbeddedButton

 

To change the name of the command button, or the text that is displayed, right-click on the command button and click on Properties.

VBA_Buttons ActiveX Properties

Amend the 1) name of the button, 2) caption of the button and 3) format of the text as required.

VBA Buttons ActiveX Change Properties

To add code to the button, right-click on the button once more and select View Code.

VBA_Buttons ActiveX View Code

This will automatically create the click event for the button in the VBE Editor. (the click event is the most common event for command buttons and is created by default).

VBA Buttons ActiveX Click Event

Type the code you require into the event that has been created.

Close the VBE Editor to return to your worksheet.

Click once more on your new button.  You will notice that macro does not run, but the button gets selected.  This is due to the fact that when you create an ActiveX button, you are automatically put into Design Mode. 

VBA Buttons ActiveX DesignView

To run the code behind the button, click on the Design Mode button on the Ribbon to go out of Design mode, and then click the button.

VBA Buttons ActiveX Clicked

 

Adding a Macro to a Shape in an Excel Worksheet

In the Ribbon, select Insert > Shape.

VBA Buttons Insert Shape

 

Select the shape from the drop down, and then drag in the Excel worksheet to create the shape.

VBA Buttons DragShape

Right-click on the shape, and select Assign Macro.

VBA Buttons AssignShape Macro

Select the macro to assign, and then click OK.

VBA Buttons Shape Hand

When you move your mouse over your shape, the mouse cursor should turn into a small hand.  Click on the shape to run the macro.

Adding Command Buttons to VBA Forms

Creating VBA UserForms is a big part of programming in VBA.  It allows you to build a professional looking user interface to communicate with the users of your VBA application.  It also allows you to totally control the user in what they are doing to your workbook.  To learn how to create UserForms in VBA, click here.

Once a UserForm is created, and controls have been added to a UserForm, buttons can be added to perform functions such as OK and Cancel.

In the Toolbox, select the Command Button control, and then click and drag in your form to create the button.

VBAControls CommandButton

Using the Caption Property, change the caption of the command button to OK, and the Accelerator to “O”

VBAControls Button Properties

 

The purpose of the Accelerator is for the user to use the keyboard to activate the button, in this case Alt+O would activate the button.

VBAControls Button Accelerator

Once we have created the command button, we can double-click on the command button to go to the VBA code.  Double-clicking on the command button in the form will automatically create the Click Event for the button as this is the event that is most commonly used by command buttons.

VBA Buttons Click Event

We can now type our relevant VBA code within the procedure that has been created.

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! vba save as


Learn More!