When you develop expertise in writing code in Microsoft Visual Basic for Applications (VBA) in Microsoft Excel, you can automate almost all your tasks and improve your efficiency and productivity. However, if you are a software developer, and you write code for someone else, the individual may find it difficult to execute the macros from the code window of Microsoft VBA.
Fortunately, Excel also allows creating user forms and command buttons to execute the code. In this blog post, I will tell you how you can assign a macro/VBA Code to a Control Button or a Command Button.
First, create a test file and name it data.xlsm. Open data.xlsm, and press Alt+F11 to open the VBA editor.
Under Microsoft Excel Objects, double click Sheet 1 (Sheet1). Click Insert -> Procedure… and in the Name box, enter TestMacro and click OK.
Now enter a simple Msgbox code as shown below:
Public Sub TestMacro()
MsgBox "This macro has been executed!!"
End SubNow, we have to create a button to execute this macro. Proceed as follows:
- Click File -> Options -> Customize Ribbon
- Under Main Tabs, select Developer and click OK
- On the Developer Tab, in the Controls group, click Insert
- Select Button from Form Controls
- Click on the worksheet where you want to place the button, Assign Macro dialog box will appear
- Select your macro, which is Sheet1.TestMacro in this example.
- If you want the macro to be available only in the current workbook, then select This Workbook in Macros in.
- Click OK
- Right-click the button and select Edit Text
- Enter the caption Process Data
- Click anywhere on the sheet to exit the edit mode.
We are done. Now click the button. You will receive a message:
This macro has been executed!!
It means the button works well.
There are also other ways of assigning a macro to a button. You can read this article to find out more ways.
https://www.ablebits.com/office-addins-blog/2020/02/26/run-macro-excel-create-macro-button