On Mon, 26 Jan 2004 14:58:42 -0000, "Mervyn Thomas"
wrote:

Thanks Hank ! all that was very usefull and informative - only one
question - How do you call a macro that's in an XLA ? I couldn't figure out
how to do that!
Mervyn

Sorry for the delay; I still have a few residual computer problems and
wasn't on line yesterday.

There are a number of ways of doing this. If you manually type the
macro name into the appropriate box, you can run the macro straight
from the Run Macro dialog. You won't be able to see the name in the
list, but if you enter (for example)

TestAddIn.xla!MyProcedure

into the "Macro Name" text box and click the [Run] button, you'll be
able to run it. (In most cases it won't be necessary to qualify the
procedure name with the name of the add-in and the exclamation mark.
However if there are other macros with the same name around the place,
that would ensure that you run the correct one. For example, if you
have a Sub in the current workbook which is ALSO called MyProcedure,
then if you just type the procedure name into the run macro dialog
that'll be the one to run. Add the reference to the .xla as I've
shown, though, and the procedure in THAT file will run instead.)

You can also assign the macro to (say) a command button from the Forms
toolbar in the same way through the Assign Macro dialog. The only
thing to watch out for here is that even if you qualify the macro with
the .xla name, it won't stick; if the button finds a local procedure
with the same name as the one in your add-in, it will always run the
local one in preference. (If you look at the "Assign Macro" dialog,
you'll see why; it reverts to the unqualified macro name every time.)

To run the procedure from another VBA project (say, from the Click
event of a command button from the Control Toolbox toolbar, or even
from a standard procedure in a workbook), probably the simplest way is
to use Application.Run. For example:

Application.Run ("TestAddIn.xla!MyProcedure")

Hope this helps.

---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
* Please keep all replies in this Newsgroup. Thanks! *