Add-in doesn't add up

[wisgro]

I use Excel 2002, a.k.a. Excel XP. I created a macro and it works exactly the way I want it to. The first time I ran it excel told me the macro is being used by another worksheet, a.k.a the hidden file. I had to unhide then close the original macro file each time in order to use it in my spreadsheets. I read that saving the macro as an add-in would eliminate this annoyance. Yesterday I followed MS KB article # 211563 instructions for saving a macro file as an add-in file. Easy as pie. I saved the macro file as an add-in file with the xla extension per the MS instructions. Selected the file name in the add-in list (Tools>Add-ins) thus activating the add-in. I opened one of my workbooks, clicked the button that has the macro assigned and it worked slicker'n custard! No error message or hidden macro file. The add-in worked in workbook I opened. Today I opened the same excel workbooks, checked to make sure the add-in is still selected. I clicked the button assigned to the macro and got the pesky error message again that the macro is being used by another worksheet. I unhid the macro worksheet and the button works but apparently the add-in doesn't any more. I renamed the macro file in an attempt to disable it thinking the add-in would take over. No way. I get the error message that the macro file can't be found. What the??? Any ideas why the add-in doesn't work after the first try?

[shg]

The exact error message?

[wisgro]

Thanks for responding so quickly. This in nutty. I tried to duplicate the error so I could copy and post it but now the add-in works and there is no hidden macro file. I changed the file name back to the original macro file name, opened a workbook, clicked the button and it worked. I don't get it. Here are the steps I took creating the macro: 1. created the macro with VBA. 2. after testing I created a button and assigned it to the macro. 3. After reading MS how to I saved the macro file as an add-in, (xla) file. 4. selected the add-in in the add-in list. The add-in worked fine yesterday. Today the add-in wouldn't work and the macro file kept opening in its hidden state. I attempted to use the add-in with 3 workbooks that have the button and add-in selected yesterday. I then changed the macro file name and got the error message that the file couldn't be found. Changed the macro file name back and now the add-in works and the macro doesn't open and hide. The original error message said that the macro was being used by another worksheet and couldn't be opened. I know that one must then unhide the worksheet then close it in order to use the macro. But I thought my troubles were over when I created the add-in. I have read on the forum many, many moons ago that both excel macros and add-ins can display erratic behavior.

[shg]

An add-in is a hidden workbook. You should create and save the add-in, close the workbook, install the add-in, and then assign the button to the macro.

When you have an add-in, you cannot see the procedure names in the (Alt+F8) macro dialog; you have to type or paste them in. If you can see them, you aren't referring to a procedure in an add-in.

I have read on the forum many, many moons ago that both excel macros and add-ins can display erratic behavior.

No doubt, but Excel operators are generally much more erratic than Excel

[wisgro]

I followed your instructions if by install add-in you mean checking the box next to the add-in on the list Tools>Add-Ins. (That's the way I have activated other add-ins.)
I opened a new workbook, checked the add-in box, created a button released the mouse button and "Assign to..." does not appear. (MS instructions say the Assign menu will appear when the mouse button is released. Perhaps a macro has to be activated first?) I opened the macro list and it is blank. I typed personal.xla, the file name I used for the add-in. I get an error message that says this isn't a macro. According to everything I have read before I started this I thought the add-in process was simple enough. Turns out I was wrong. The original macro only works with the workbook in which I first created it. Macro aside I want it to be an add-in. When I check the box next to other add-ins they appear in the Tool menu. I click on them and they run. Obviously I've missed something.

[shg]

Personal.xla is probably not a great name for an add-in. If you want to put the macros in your Personal.xls file, that's fine. The rationale for creating an add-in is typically because you want to distribute it, and your Personal.xls workbook typically doesn't fall into that category.

For simplicity, how about calling it myAddIn.xla for now?

Create a new workbook and add a macro in a code module:

Please Login or Register  to view this content.

Save the workbook as myAddIn.xla

Exit Excel, then open Excel with a new workbook

Add in myAddIn.

Add a button or autoshape to the sheet.

Right-click the shape, select Assign macro, and type myAddIn.xla!Hello.

Click off the shape (to unselect it)

Left-click the shape.