getting a macro to work in an xla

[techinvt]

So I have created this long involved macro that finally works. I created a new toolbar, put a button on that toolbar, and pointed the button the the macro. Everything is good. Now I want to save it as an xla so I can just put it in everyones excel startup folder, but I'm missing something here. When I save it as an xla the macro button stops working. It tries to point to the macro in another workbook. I have tried creating the xla from scratch and importing the bas and form file into it, but I can't figure out how to access it in the xla file. If I create a SPREADSHEET and import the bas file and form file, it appears as a macro in the macro list. But it wont appear in the macro list in the xla file - I guess because there is no actual workbook open in an xla file? So I guess my question is: How do I create an xla file to place in the ...excel\xlstart folder that contains the macro (that I have already created and know how to import)and a button on a toolbar, so that the button shows up when excel is opened and the macro works? Thanks in advance.

 

[ClulessChris]

Take a look at the addin manager to get excel to see your xla

Everybody body is somebodys Nutter.

 

[combo]

There are some traps in the whole procss:
1. the toolbar macros point to 'xls' file, when you save it as an add-in, the toolbar still points to original file,
2. the distributed add-in can be installed under variable path,
3. you can stay with the toolbar visible without file with macro opened.

The typical solution for this is a dynamic toolbar. It can be added/removed when the add-in is opened/closed.

Add-in is close to regular workbook, except that it can't be revealed or saved with excel interface. However it offers additional functionality - install/uninstall is a procedure to switch on/off the add-in; this feature tells excel how to handle it when you start excel too. Therefore you should avoid placing it in the xlstart path.

combo

 

[techinvt]

cluless:
I don't see anything in the add in manager...

combo:
Can you clarify a little bit? It sounds like you're talking about what I need, but you didn't provide enough detail for me to really follow what you were talking about so I could try any of it.

Thanks

 

[combo]

You shouldn't open the add-in alike regular workbook (however you can). With Tools>Addins.. you can add any add-in to the list and next install/uninstall it with the checkbox in the add-ins dialog.

Installed add-ins (ticked), wherever they are, are opened when you start excel, there is no need to copy them to xlstart path. If you want to stop it (either close or force not to open on startup) it is enough to uninstall it (untick on the add-ins list.

Add-in offers two additional workbook events (AddInInstall, AddinUninstall), they are handy to keep additional add-in settings (if any) between excel sessions, as it works the best as extension of excel standard interface.

Add-in behaves as a regular workbook too, when you open an excel with add-in installed (or install the add-in during excel session) the file is opened and 'Open' event fires, on the other side 'BeforeClose' event fires (close excel or uninstall the add-in.

The last two events (Open, BeforeClose) are, in my case, most useful to manage the interface connected with the add-in. It includes creation/destruction of (temporary) toolbar(s), menus etc. You can easily assign required macro to a given commandbar control.

Concluding, in your case I would rather work out a workbook that provides macros and creates/deletes temporary interface commandbar (search this site or ms help for examples), and next save it as an add-in, that could be installed and uninstalled.


combo