How to Add a VBA Button in Excel?

How to Add a VBA Button in Excel?

When using a workbook that incorporates VBA code, you can add a macro button to make it easier for other Excel users to run the code without knowing the VBA code. Excel users use such buttons to access most of the macros in the worksheet easily.

Adding buttons to the worksheet will help expose other users to the custom functionality of your workbook. When a VBA button is clicked, it automatically activates a macro, and it eliminates the need to use the Developer tab to create or access macros.

Types of VBA Buttons for Worksheets

1. Form Control Buttons

Form control is one of the main ways of creating a button that runs a macro when a user clicks on it. It works in the same way as ActiveX control buttons, but they differ in several aspects.

Form control buttons give the impression of being pressed when a user clicks on them, and their appearance resembles buttons used in older versions of MS Windows. The buttons allow users to change the type, size, and color of the font. However, the button color cannot be changed.

Follow the following steps to create a Form Control Button:

1. Go to the Developer tab and click Insert under the Control section.
2. Click the Insert button in the drop-down list that opens.
3. Position your cursor in the worksheet location where you want the button to be created.
4. A pop-up window will appear. Click Assign Macro and confirm.
5. Once the button is created, right-click on it, and click Form Control. It enables you to manage the button’s properties.

To change the button label, right-click on the button and select Edit Text from the drop-down list. You can also re-assign the macro by clicking the Assign Macro option from the drop-down list.

2. ActiveX Control Button

ActiveX Control buttons are the best alternative to Form Control buttons, and they allow more formatting options than the latter. When creating buttons using ActiveX, users are required to add the event macro to the sheet model. The macro will be triggered when the macro is clicked.

ActiveX Control buttons also allow users to add other event triggers, such as double-click. It lets users perform additional actions when the button is double-clicked.

Follow the below steps to create an ActiveX Control Button:

1. Open the Developer tab and go to the Control section. Click Insert and scroll to the ActiveX controls.
2. Click the first button below the ActiveX controls (Command button).
3. Position your cursor on the worksheet location where you want your button to appear.
4. To create the macro, click View Code in the Controls group under the Developer tab to launch the VBA editor. Choose CommandButton1 on the subprocedure (on the left). Press Click on the drop-down list on the right side of the VBA editor window.
5. In the subprocedure, enter the name of an existing macro or create your own VBA code. You can find existing macros in the workbook by clicking Macros in the Code section. You can add multiple macros by entering the name of each macro on separate lines in the VBA editor.
6. Click the Design mode to turn it off and close the VBA editor before running the VBA code. Then, create the ActiveX button create in the worksheet to run the macro.
7. To change the button properties, click the Properties option under the Controls group section. Alternatively, right-click on the button and select Properties in the dropdown.

3. Excel Shapes

Apart from creating buttons from the Developer tab, Excel users can use Excel Shapes to create the buttons. The advantage of using Excel Shapes over Form Control buttons or ActiveX Control buttons is that shapes allow the most formatting and styling options. You can change button color, design, and font to make the buttons look modern.

Here are the steps to create a button in Excel using Excel Shapes:

1. Click on the Insert tab and then click on Shapes.
2. Select the preferred shape of your button (we selected a rounded rectangle).
3. Drag and drop the selected shape to the preferred location on the worksheet to set the size of the shape.
4. Right-click on the button and select Edit Text to change the button label. You can also format the button using the tools available under the Home tab.
5. To specify a macro, right-click on the button and select Assign Macro.
6. Select the macro from This Workbook list to make the macros shareable with other users who use the workbook. Press OK to confirm the macro.
7. Click the button to trigger the macro operation.

Additional Resources

Through business intelligence and data analysis courses, training, and exercises, anyone in the world can become a great analyst. To keep advancing your career, the additional CFI resources below will be useful:

• Excel VBA Examples
• VBA in Excel
• VBA Quick Reference
• Top 8 VBA Routines for Excel Reporting
• Transitioning from Excel to Python
• See all Excel resources