Find and access elusive add-ins in Excel 2007

When you want to easily package your favorite macros and send them to a colleague or client, an add-in is the way to go. Once installed, the add-in provides all of the macros you included in your add-in on the new system. Excel has several add-ins that come with the application, but the ability to create your own can help you share specialized macros with others — and they don’t need to know how it works.

Unfortunately, Excel 2007 isn’t exactly add-in friendly. Keep reading if you’re frustrated with an add-in that worked like a charm in previous Excel versions, but now seems impossible to locate.

Install add-ins through the new Office button
Excel 2007 has a completely new interface so you may not know where to find the familiar Add-in Manager. The good news is that it’s there — it’s just hidden within the new Office button.

To install an add-in:

1. Click the Office button and then click the Excel Options button.
   
2. Click Add-ins in the left panel. Excel displays a list of your active and inactive add-ins.
   
3. Choose Excel Add-ins from the Manage dropdown list and click the Go button. Excel opens the familiar Add-ins dialog box from earlier versions.
   
4. Click the Browse button to open the Browse dialog box.
   
5. Navigate to the add-in you want to install, select it, and then click OK. The add-in displays in the Add-ins dialog box with its corresponding check box selected.
   
6. Click OK to return to your spreadsheet.


7. Go back to the Excel Options window and you’ll see the new add-in listed in the Active Application Add-ins section.
   

New file format: Excel 2007 uses a different file extension for add-ins. Instead of .xla, 2007 add-in files have the extension .xlam.

I can’t find my add-in!
Unfortunately, installing add-ins isn’t the hard part when it comes to 2007. When you need to run the macros you packaged into the add-in, that’s when things get tricky.

Locate add-ins that come with Excel
You can locate the Analysis Toolpak, the Lookup Wizard and other familiar add-ins based on what they accomplish. You’ll find these pre-packaged add-ins in one of two places: the Formulas ribbon’s Solutions area or the Data ribbon’s Analysis area.

Find custom add-ins
The most frustrating aspect of using add-ins in Excel 2007 is finding a way to run macros included in the add-ins you’ve created and installed.

The problem: When you produce an add-in for someone else, you often create a way for the user to quickly run the macros you’ve included, such as a new menu item or a custom toolbar button. Well, there are no menus in 2007 and the toolbars have morphed into ribbons. So what can you do?

In theory, when you install an add-in, Excel 2007 displays the Add-ins ribbon, which should include any custom menus or toolbars. We’ve found that this isn’t necessarily the case. Even after hours of trying to get the Add-ins ribbon to appear, it can still be elusive.

The most reliable way we’ve found to run a macro from your installed add-in is to add the macro to your Custom Quick Access Toolbar. This toolbar is the only toolbar you can customize in 2007 — there isn’t much you can do to change the ribbons unless you’re an XML expert.

To add a macro to your Quick Access Toolbar:

1. Click the Office button and then click the Excel Options button to open the Excel Options window again.


2. Choose Customize from the panel on the left-hand side of the window.
   
3. Select Macros from the Choose Commands From dropdown list.
   
4. Choose the macro you want to create a toolbar button for and click the Add button to move it to the list box on the right.
   
5. Select the newly added macro from the list box on the right and click Customize.
   
6. Select an icon to represent the macro on the Quick Access Toolbar and enter a new display name if necessary.
   
7. Click OK to return to the Excel Options window and click OK again to dismiss it.
   
8. You’ll immediately see a new toolbar button on the Quick Access Toolbar, and the name you designated displays as a ScreenTip.

Related Courses
• Excel 2000, 2002, 2003, 2007 & 2007 New Features
• 4002 Forecasting and Trend Analysis Using Microsoft Office Excel 2003
• 4003 Summarizing Microsoft Office Excel 2003 Data to Make Better Business Decision

Link : Elevate 12/07

Print this post