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 so it can be ran 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 and 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 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 then 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 and 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.
The next time you start Excel, your Personal Macro Workbook will load, but it is hidden by default, but you can easily view it –
- View Tab
- Window Group
- 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
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.
Don’t forget to sign up to the Excel at Excel Newletter for 3 free Excel tips the first Wednesday of the month. Just click on the Sign Up Form to the right or use the link below.