Creating A Macro Button In Excel?
We can create button macro in Excel to run a macro at the click of the button to execute an action. And we can make an interactive macro push button using the Shapes, Form Controls, and ActiveX Controls Excel options.
Users can create button macro in Excel for automating tasks such as worksheet printing, data filtering, and data evaluation.
For example, the table below lists students and their scores in various subjects.
And the requirement is to filter the subject data according to the subject name specified in cell F1.
We can use the Shapes option in the Insert tab and write a VBA code in the VBA Editor to create and run macro button in Excel.
First, we develop a VBA code in the VBA Editor to filter the required data.
Next, insert a rectangle using the Shapes option in the Insert tab to represent a macro button at the desired location in the sheet, say, next to cell F1. And use the options in the Format tab to format the shape features to fit our requirements.
And then, right-click the shape and choose the Assign Macro option from the context menu. The Assign Macro window will open, where we must select the macro we created and click OK to link the VBA code to the inserted shape and create button macro in Excel.
And now, we can enter the subject to filter in cell F1 and run macro button in Excel by clicking it to filter the required subject data in the source dataset.
Further, we can edit macro button in Excel using the Format tab options even after assigning the required macro to the button.
Table of contents
Key Takeaways
- The options to create button macro in Excel enable one to insert an interactive button in a sheet and click it to run a macro assigned to it.
- Users can create a macro button in Excel to automate actions such as data filtering and printing.
- We can create a macro button using the Shapes option in the Insert tab, and Form Controls Button and ActiveX Controls Command Button in the Developer tab.
- We must have adequate VBA coding knowledge to create button macro in Exceland use it in the most practical ways.
3 Methods To Create Button In Macro Excel
The three methods to create button macro in Excel are as follows:
- Using Shapes
- Using Form Control Shapes
- Using ActiveX
Let us see each method with an example.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
#1 – Using Shapes
The steps to create button macro in Excel using Shapes are as follows:
- Ensure the source dataset is correct to ensure we achieve the desired output.
- With the active sheet open, press Alt + F11 to access the VBA Editor.
- Choose the applicable VBAProject, and using Insert à Module, open a new module window.
- Enter the required VBA code in the module window, which will give the required result at a click of a button.
- Next, go to the active sheet and select Insert à Shapes to insert a shape at the required location in the worksheet.
- When we insert the required shape, the Format tab in the ribbon gets enabled. We can use the Format tab options to edit the shape elements to suit our requirements.
- Right-click the shape and choose Edit Text from the context menu to update, which the shape must display as a macro button.
- Right-click the shape to choose Assign Macro from the context menu to open the Assign Macro window, where we must choose the VBA code we created in steps 2 to 4. And then click OK.
Thus, once we link the required VBA code to the inserted shape, the shape becomes a macro button. We can click the button to run the VBA code without going to the VBA Editor to run it.
Basic Example
The table below lists books and their authors.
And the requirement is to show the data in the Proper case.
Then, here is how to create button macro in Excel to achieve the required output.
Step 1: Press Alt + F11 to open the VBA Editor.
Step 2: Choose the applicable VBAProject and choose the Insert tab – Module to open the new module window.
Step 3: Enter the VBA code in the module window to perform Excel change case for the chosen data to the Proper case with the click of a macro button.
Step 4: Click Insert – Shapes – Rectangle shape.
Step 5: The cursor will appear as a Plus sign. Drag the cursor to insert a rectangle of the required size at the desired location in the sheet to use as a macro button.
Step 6: As the shape is selected, the Format tab will be enabled in the excel ribbon. Use the required options in the Format tab, such as the Shape Fill, to change color of macro button in Excel.
Next, right-click the shape to choose Edit Text from the context menu.
The shape will be in an editable mode. Thus, we can update the text we wish to display in the macro button.
And edit macro button in Excel using the Font settings in the Home tab to ensure the button appears according to our requirements.
Step 7: Right-click the shape to choose Assign Macro from the context menu.
The Assign Macro window will open, where we must choose the required macro from the list and click OK.
Thus, we achieved the required macro button in the desired format.
Finally, select the cell range A3:B12 and hover the cursor on the macro button to see the cursor as a hand.
Click the macro button to view the selected data in the Proper case, as shown below.
Further, once we link the VBA code to the shape, we can copy and paste macro button in Excel any number of times in any part of the workbook. And then we can use it to achieve the required outcome.
The next two methods require us to enable the Excel Developer tab in the ribbon. And if the tab is not visible in the ribbon, here is how to enable it.
1: Choose File – Options.
2: The Excel Options window opens, where we must click the Customize Ribbon option in the left menu to open the corresponding tab.
And then, check the Developer box in the Main Tabs list and click OK.
And now, we can view and access the Developer tab from the ribbon.
#2 – Using Form Control Shapes
The steps to create button macro in Excel using Form Control Shapes are as follows:
- Ensure the source dataset is correct to ensure we achieve the desired output.
- With the active sheet open, press Alt + F11 to access the VBA Editor.
- Choose the applicable VBAProject, and using Insert à Module, open a new module window.
- Enter the required VBA code in the module window, which will give the required result at a click of a button.
- Next, go to the active sheet and select Developer à Insert à Button in the Form Controls category to insert a button at the desired location in the sheet.
- Right-click the button and choose Edit Text from the context menu to update the text in the button.
- Right-click the button to choose Assign Macro from the context menu to open the Assign Menu window, where we must choose the VBA code we created in steps 2 to 4. And then click OK.
Thus, once we link the required VBA code to the inserted button, the button becomes a macro button. We can click it to execute the VBA code without going to the VBA Editor to run it.
Furthermore, we get limited options to modify the button’s format. But we can copy and paste macro button in Excel any number of times and positions to suit our requirements.
Basic Example
The table below lists a set of numbers.
The requirement is to multiply an integer number with each number in the dataset at a click of a button and show the output in the same cells as the source data.
Then, here is how to create button macro in Excel to use it and perform the required task.
Step 1: Press Alt + F11 to open the VBA Editor. And choose the required VBAProject and Insert – Module to open a new module window.
Step 2: Enter the VBA code in the module window to allow the user to enter an integer number to multiply with the given data set.
Step 3: Go to the active worksheet and choose Developer à Insert à Button in the Form Controls category.
Step 4: The cursor will appear as a Plus sign. Drag the cursor to create a shape of the required size at the desired location in the sheet to use as a macro button.
Please note that the macro button formatting options are limited when using this method. For instance, we cannot change color of macro button in Excel. The options appear greyed out in the Format tab.
However, we can right-click the button and choose Format Control from the context menu to format specific button features.
Click the required tab, set the specific formatting options and click OK in the Format Control window.
Step 5: Right-click the button to choose Edit Text in the context menu.
And update the text we wish to display in the macro button.
Step 6: Right-click the button to choose Assign Macro from the context menu.
The Assign Macro window will open, where we must choose the required macro and click OK.
The macro gets assigned to the button, and we achieve the required macro button.
Finally, we can select the source dataset and hover the mouse over the macro button to get the hand cursor.
Click the macro button to get the following message box.
Next, enter the integer to multiply with the numbers in the source dataset and click OK.
And we will obtain the following output, with the product of each number in the dataset and the specified integer in the corresponding cells.
#3 – Using Active X
The steps to create button macro in Excel using ActiveX Controls are as follows:
- Ensure the source dataset is correct to achieve the desired output.
- Select Developer à Insert à Command Button in the ActiveX Controls category to insert a command button at the required position in the worksheet.
- Right-click the button to choose View Code from the context menu to open the VBA Editor. The corresponding Microsoft Excel Object’s module window will open in the VBA Editor, showing a private function to update the required code.
- Once we enter the code in the module window, click the Save icon in the top menu.
- Go to the active sheet where the Design Mode option in the Developer tab will appear chosen. And click Properties in the Developer tab to open the Properties window of the VBA Editor.
- Format the command button using the options in the Properties window.
- Click Developer à Design Mode to unselect it and exit the command button edit mode.
Thus, once we update the required VBA code of the inserted command button, the button becomes a macro button. We can click it to execute the VBA code without going to the VBA Editor to run the code.
Basic Example
The table below lists item codes, invoice numbers and dates and units purchased data.
However, the data contains blank cells with a single space character, which we must highlight at a click of a button.
So, here is how to create button macro in Excel using the ActiveX Controls and obtain the required output.
Step 1: Select Developer – Insert – Command Button in the ActiveX Controls category.
The cursor will be a Plus sign. Drag the cursor to create a command button of the required size at the desired location in the sheet to use as a macro button.
Step 2: Right-click the command button and choose View Code from the context menu.
The VBA Editor will open, with the module showing the private Sub procedure for the corresponding Microsoft Excel Object.
Step 3: Update the required VBA code in the active module window to highlight blank cells with a single space character in the chosen cell range.
And click the save icon in the menu.
Step 4: The Design Mode option in the Developer tab appears selected, enabling us to format the command button.
However, most of the features in the Format tab appear greyed out.
But, we can click Developer – Properties to format the button from the Properties window in the VBA Editor.
For example, we shall update the button BackColor in the Alphabetic tab, as shown below.
Likewise, we can click the Categorized tab in the Properties window and use the options to set the required macro button format.
Once the required changes are updated, the button appears as depicted below, with the Design Mode option selected in the Developer tab.
[Alternatively, we can open the VBA Editor and click the required Microsoft Excel Object.
And click View – Properties Window to open the Properties pane.
And click the Design Mode icon in the top menu.
The Properties pane will show the option to update the CommandButton settings.
We can update the required settings for the specific command button.]
Further, we can right-click the command button and choose Format Control from the context menu to format specific button features.
We can click the required tab, choose the specific settings options and click OK in the Format Control window.
Step 5: Choose Developer – Design Mode to unselect the option and exit the command button format edit mode.
Next, select the required cell range and hover over the command button to view an arrow as the cursor.
And click the command button to view the required cells highlighted in the chosen range.
Important Things To Note
- We can create button macro in Excel using Shapes in the Insert tab, which we can format, copy and paste across the workbook.
- The properties formatting options for the macro button, created using the Form Controls Button, are limited. But we can copy and paste it at the required locations in the workbook.
- We can use the Properties option in the Developer tab to format the macro button created using the ActiveX Controls Command Button. However, copying such buttons at other locations in the sheet may not yield the correct output.
Frequently Asked Questions (FAQs)
You can make a floating macro button in Excel using the ActiveX Controls option.
Let us see the steps with an example.
1: Choose Developer – Insert – Command Button in the ActiveX Controls category.
The cursor appears as a Plus sign. Drag it to create a command button of the required size in the desired location in the active sheet.
2: Right-click the command button to choose View Code from the context menu.
The VBA Editor opens, with the module window showing the private Sub procedure of the corresponding Microsoft Excel Object.
3: Enter the VBA code to make the macro button float in the active sheet.
And click the save icon in the top menu.
The command button will appear at the original position with the Design Mode option chosen in the Developer tab.
4: Select Developer – Design Mode to unselect it and exit the command button edit mode.
Thus, the macro button will also move when you move up and down the sheet. And thus, it appears floating in the worksheet.
We can create a print macro button in Excel using the following steps:
1. Ensure the dataset to print is accurate.
2.Press Alt + F11 to access the VBA Editor.
3. Choose the required VBA Project and then Insert – Module to open a new module window.
4. Enter the following VBA code to create a print button for a print dialog box.
Sub DialogBox()
Application.Dialogs(xlDialogPrint).Show
End Sub
5. Go to the active sheet and select Developer – Insert – Button in the Form Controls category.
6. Drag the Plus sign cursor to create a button of the required size at the required location in the sheet.
7. Right-click the button to choose Edit Text from the context menu and update the text we wish the print macro button must display.
8. Right-click the button to choose Assign Macro from the context menu. The Assign Macro window will open, where we must select the macro created previously and click OK.
Thus, now we have a macro button for printing the sheet content. We can select the required data and click the macro button to print.
We can create a macro button in Excel for Mac using the following steps:
1. Select Excel – Preferences – Ribbon & Toolbar.
2. Open the Customize the Ribbon category and check the Developer option box in the Main Tabs list. And click Save.
3. Select Developer – Button.
4. Place the cursor on the sheet where we must start creating the button of the required size.
5. Right-click the button to choose Assign Macro from the menu.
6. Select the required macro from the list in the Assign Macro window and click OK.
7. Finally, right-click the button and select Format Control to update the macro button’s control properties.
Download Template
This article must be helpful to understand the Create Button Macro In Excel, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to What Is Create Button Macro In Excel. Here we learn the methods to create button macro in Excel with examples & points to remember. You can learn more from the following articles –
Leave a Reply