In this tutorial, you will learn 6 ways to run macro in Excel.
After creating macro or VBA user must be provided some interface to run that macro.
You will learn various methods like using buttons (create and assign), using a shortcut key, using Excel ribbon & more to run VBA in Excel.
Run macro using button |create macro button
Here you will see how to create a macro button and assign macro to a button in Excel.
To create a macro button you can use shape, text box, form control button and active x control button.
Macro button using shape
To insert a shape in Excel sheet click on the Insert tab then click on Illustration then click on Shapes dropdown to get various available shapes.
Select the shape of your choice and double click on it to bring it in Excel sheet. Adjust shape dimension, color, border and shape effects as per your requirement.
You can write some relevant text on the shape button to suggest what it does.
To assign macro to the button right click on the button then click on Assign macro.
Select a sub or procedure to assign or run when the button is clicked then click on Ok.
Note:- Private Sub or procedures are not shown in the Assign macro list. Remove private keyword before Sub to make it visible in Assign macro list.
Excel VBA private sub and public sub (private vs public sub in VBA)
After assigning a macro to the button you can run the macro by clicking on the button.
Form control macro button
To insert Form control macro button in Excel sheet click on Developer tab.
Activate Developer tab and insert VBA code
Click on Insert dropdown option. In Form controls section click on button.
Select macro to assign to a button then click on the Ok button.
To change the button name, first select the button by right-mouse clicking then double click on the button and edit text.
Active X control macro button
Running macro using Active X control button is my favorite. I prefer this method than using shapes or Form control button.
To insert Active X control button on the Developer tab.
Click on Insert dropdown option. In Active X controls section, double mouse click on button.
Note:- To select Active X control button or objects first activate Design Mode by clicking on it.
You can select then drag and resize button.
You can change button caption, color and button name by selecting button then clicking on Properties present in the Developer tab.
Keeping Design mode active double click on the button to bring On Click event code for that button. In VBA IDE this will look like the below.
In the below Sub cmdButtonMyMsg is button name and _Click means on clicking this button run whatever code is written between Sub and End Sub.
Private Sub cmdButtonMyMsg_Click()
End Sub
In the above image, you can see Module1.My_msg code will run when the button is clicked.
Note:- Deactivate the Design Mode to use Active X button then click on it to run the macro.
Advantage of Active X control button over Form control and Shape button
Active X control button will always work and refer to Sub (macro) of the workbook in which it is present even after changing the workbook name or saving as the workbook.
In the case of shape button or form control button you need to reassign macro when workbook name is changed or after saving as the workbook.
Not doing so, the button will always refer to the macro of the original workbook in which it got created.
Run macro from Excel ribbon
To run a macro from Excel ribbon click on the Developer tab then click on the Macros option.
Alternatively, you can press ALT+F8.
Now, you will get Macro pane with list of macro names or procedures.
Select the macro you want to run then click on Run button to run macro.
Run macro from VBA IDE (VBA Editor)
To run macro from Visual Basic Editor (VBA IDE) click on the Visual Basic option present in Developer tab.
Alternatively, you can press ALT+F11 shortcut key to open VBA IDE.
Then double click on Module or sheet module or Thisworkbook which contains your required macro.
From the right side drop-down list select macro, this will bring a cursor in that macro line.
Alternatively, you can manually navigate and place the cursor on one of the lines of the macro which you intend to run.
Click the Run button or press F5 function key to run macro from VBA IDE in Excel.
Run macro by creating shortcut key
To create macro shortcut key in Excel go to Developer tab.
Click on the Macros option. Alternatively, you can press ALT+F8 key.
In the Macro pane select the macro for which want to create shortcut key.
Click on Options.
In Macros Options write a letter in shortcut key section.
CTRL+SHIFT+letter shortcut key combination is assigned to a macro if the caps lock key is on.
CTRL+letter shortcut key combination is assigned to a macro if the caps lock is off.
Click on the Ok button.
Now, you can press that shortcut key to run VBA in Excel.
Note:- You can not assign the same shortcut to two macros in the same workbook.
Run macro using custom ribbon tab
Follow the below link
Run macro using custom ribbon tab
Run macro on workbook opening
To run the macro (VBA code) on opening a workbook you need to create workbook open event.
Follow the below step to auto-run code or macro when the workbook is opened.
Press ALT + F11 to open VBA IDE.
In VBA IDE click on the Thisworkbook module.
Copy-paste the below code in the Thisworkbook module.
Private Sub Workbook_Open()
''your code lines
End SubAlternatively, to bring the above code section automatically ,select workbook from left side drop down.
Now, whatever code you write between these code lines will run automatically when the workbook is opened.
If you save the below code in the Thisworkbook module of a specific workbook then after opening the workbook message box is shown automatically.
Private Sub Workbook_Open()
MsgBox "Hi LearnYouAndMe"
End Sub
Note:- Make sure event is enabled. Without enabling event no event (like workbook open) will not work.
To enable the event run the below line in VBA Immediate window.
Application.EnableEvents=True
Suggested Readings