Hello Excellers, and today let’s look at how to assign an Excel Macro to a button or assign a button to an Excel Macro, however you want to say it!.
There are a few ways to run a an Excel Macro that you have created. A brief summary of which can be seen below.
- Run the Macro from the Immediate Window in the Visual Basic screen. (you can read more about Running An Excel Macro From The Immediate Window
- You can specify that a Macro runs when a specific event occurs. For example opening a workbook, saving a workbook, or closing a workbook.
- A Macro can run from another sub procedure that you have written
- A customised item you have added to the Ribbon, and that can trigger a Macro when you select it.
- Run the Macro from a Button you have added to either the Quick Access Toolbar, or on the Excel worksheet.
So, let’s take a look at number 5 above today, assigning a button either on the Quick Access Toolbar or on the actual Excel worksheet.
Run A Macro From A Button On The Quick Access Toolbar.
Once you have created your Macro, there are just a few short steps to get attached to a button on the Quick Access Toolbar.
- File – Options- Quick Access Toolbar
- In the Choose Commands From – select Macro
- Select the Macro you want to attach to
- Click Add to move the Macro to a list of buttons on the Quick Access Toolbar
- If you want to replace the Icon then click Modify
- Under Symbol, select the one you want
- Under Display Name – change the name if you want to use a more friendly name
Run A Macro From A Button On An Excel Worksheet.
- Ensure the Developer Tab Is Visible. If it is not follow the steps here
- Developer – Insert – Form Control – Button
- Draw the button in the position that you want it on your spreadsheet
- The Assign Macro Dialog Box will appear
- Choose the Macro you want attached to the button and hit Ok
- You can further customise the button if you want to by using the Format Tab in Drawing Tools
That’s is it, your Macro is ready to use with the push of a button, or selection in the Quick Access Toolbar.
If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
If you want to see all of the blog posts in the Macro Mondays Series or the example worksheets you can do so by clicking on the links below.