Time for another #macromonday, welcome. Today let’s look at saving even more time in Excel by attaching that great macro you have written to a button on your worksheet. The Macro can then run without having to remember the shortcut key you assigned to the macro
So, there are two parts to this procedure. The first is to place the command button on your worksheet. The second is to assign your macro to it. (well there is a third that is writing the macro of course, but that is for another time).
1. Place the Command Button on your Excel Worksheet.
Adding a button to a worksheet to invoke a macro procedure is a simply process.
- Click on the Developer Tab- if this is not enabled you need to do so, you can read my blog post here for instructions on how to do this.
- Once you can see the Developer Tab – Controls Group – Insert – Form Controls – Button
- Click on the worksheet location where you want the button to appear
- Excel will prompt you at this stage to assign a macro to the button
- Hit Ok to place the button on the worksheet.
2. Assign Your Macro to the Excel Command Button.
You can do this now at this point or you can hit cancel and assign a macro at a later time.
- To assign the Macro at the time of button creation, click on the name of the macro in the dialog box and hit Ok. Your macro will be assigned.
- To assign the macro at a later time right click on your button – hit Assign Macro
- Select the name of your macro from the Dilaog Box. In the drop down box select where to save your macro, you have the choice of All Open Workbooks; This Workbook or your Personal Macro Workbook if available- for more information on you Personal Macro Workbook you can check out my blog post here.
- My example macro simply sets the content of cells A1:D10 as bold.
- Once you select the destination and hit Ok – your Macro is now assigned to you Button on your worksheet – easy as that!.
- Just go ahead and Click That Button to test it.
Viewing Your Personal Macro Workbook
The next time you start Excel, your Personal Macro Workbook will load. The workbook is hidden by default, but easily viewed. Follow the steps below.
- View Tab
- Window Group
- Unhide
- You should now see PERSONAL.xlsb
Any macros you save to your Personal Macro Workbook will be editable but only when you choose to unhide it. It is also easy to hide the Personal Macro workkbook as well.
- View Tab
- Window Group
- Hide
If you create any new macros and save them in your Personal Macro Workbook, or edit any macros that it contains you will again be prompted to save the workbook. Just as you did the first time it was saved.
That’s all there is to it! You can now attach a macro to a command button in Excel. When you click on the button, the macro will run and perform the actions that you programmed it to do. Be sure to test your macro out by clicking on the button several times to make sure everything is working properly. If you have any questions or problems with this process, feel free to reach out to us for help.
More Excel Help And Articles
Finally, don’t forget to sign up to the Excel at Excel Newletter for 3 free Excel tips every month. Just click on the Sign Up Form to the right or use the link below.
