How to Add your Macros to QAT or Excel toolbars?

Ok, so you have written a shiny new macro to solve all the problems. The macro, solveWorldProblemsAndMakeSomeCoffee() sits nicely in your personalmacros.xlam file somewhere in C drive. You have also installed the macro as an add-in so that it is always available.

But wait!!!

How do you run your sWPAMSC everyday in the morning?

(ok, wake up now!!!, that is short for solveWorldProblemsAndMakeSomeCoffee())

One way is to,

1. Right click on sheet name
2. Select View Code
3. Navigate to the VBA Project corresponding to your personalmacros.xlam file
4. Yawn!
5. Open the module with sWPAMSC
6. Run the macro

But, shouldn’t this be faster and smarter than that?

Well, it is. You can add your macro to Quick Access Toolbar so that you can run it with just a click (or by pressing a shortcut).

Here is how you can add macros to Quick Access Toolbar (Excel 2007 Version):

1. First write your macro and save the workbook as an excel add-in.
   
2. Now, install the add-in by going to Office Button > Excel Options > Add-ins
3. Now, right click on QAT and select Customize
   
4. Select Macros from “choose commands…” option.
   
5. Now, select the macro you want to add to QAT and then press Add button
   
6. This will add your macro to QAT with default icon. You can change the icon using Modify button.
   
7. That is all.

Here is how you can add macros to toolbars in Excel 2003:

1. First write your macro and save the workbook as an excel add-in.
2. Go to Tools > Customize
3. Now, click on New button to create a new toolbar.
   
4. Give it a name. Now your new toolbar will show up in Excel 2003 UI.
5. Go to Commands tab and select Macros from left. Now drag the smiley icon from right to your new empty toolbar.
   
6. You have added a new button to your toolbar. Now click on it.
7. Excel will prompt you to assign a macro to that button. Select the macro from the list shown (it includes the macros in your add-in file).
8. That is all.

Now go solveWorldProblemsAndGetSomeCoffee()

How do you customize your QAT / Toolbars ?

Customizing quick access toolbar can be a very productive thing to do. I used to have a bunch of macros added to QAT for quickly accessing them when I was working.

What about you? How do you customize QAT or toolbars? Do you add macros? Share your experience using comments.

More tips on Excel Productivity & Customization:

• Get Excel 2003 Toolbars in Excel 2007
• Do you know these Excel 2007 Productivity Secrets? Hint: Coffee is not one of them.
• Master Excel 2007 Ribbons with this Free Guide
• More tips on Excel Productivity