Start UP File Location

  • Thread starter Thread starter Mervyn Thomas
  • Start date Start date
M

Mervyn Thomas

I have a set of macros that I only want to use in a range of workbooks so I
put them in a workbook and put this in the Tools, Options, Alternate Startup
file location which is referenced in the workbooks I want to use them on.

The problem is this appears to be a univeral setting and applies to all
workbooks in the computer.

How do I accomplish what I want to do? Someone told me to use XLA's but I
haven't figured how to actually call the macro that is in the XLA!
 
Mervyn,

This is how to call the macro

Application.Run "myAddin.xla'!myMacro", "Hello"


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Thanks Bob that does seem to work reliably and the only method that does.

I have tried variation on calling the macro direct as in
"myaddin.xla!mymacro" and sometimes it works and other times excel cannot
find the path even if I confirm that the xla is there and available in the
VBA explorer. Something strange goes on because if I move the xla and
redifine the new posituion in the addin browser Excel seems to remember the
old location rather than the new. I don't want to put the xla in the
addin direcory because that seems to only have my user rights and I want
this to be a shared addin.

Do you know if there any registry entries associated with these addin
settings
 
One way to call macros in .xla's is to add some items to a menubar. They'll
work just like the stuff you do in excel normally.

I like John Walkenbach's MenuMaker program for setting this kind of thing up:
http://j-walk.com/ss/excel/tips/tip53.htm

But once an addin is loaded, you can run it in any workbook. If that's a
problem, you could do lots of stuff to stop the code from executing based on
some kind of indicator in those other workbooks.

And you could either load the .xla each time you need it or you could even set a
reference within the VBE to that .xla for each workbook that needs it.

If you want to set a reference, you'll have to make the .xla's project name
unique. VBAProject isn't really unique!

While in the VBE for the addin, hit ctrl-R to see the project explorer.
Hit F4 to see the properties window

click on your project "VBAProject (book1.xls)" and change the (Name) property
from VBAProject to a nice name: AddinForProjXX (but use a nice name!).

Then save your .xla file.

Back to excel.
Now open each workbook.
Back to the VBE.

For each workbook that needs the reference:
Tools|references|and select AddinForProjXX

Save each workbook.

Now each time you open one of those workbooks, the addin will open.

But be aware that if you give that file to someone else, it could cause
heartache and pain, since the referenced addin might not come along!


If I were doing it, I'd just open that addin manually when ever I needed it.
But I would use John's MenuMaker program to make it easier to run.
 
Thanks Bob that does seem to work reliably and the only method that does.

I have tried variations on calling the macro direct as in
"myaddin.xla!mymacro" and sometimes it works and other times excel cannot
find the path even if I confirm that the xla is there and available in the
VBA explorer. Something strange goes on because if I move the xla and
redifine the new posituion in the addin browser Excel seems to remember the
old location rather than the new. I don't want to put the xla in the
addin direcory because that seems to only have my user rights and I want
this to be a shared addin.

Do you know if there any registry entries associated with these addin
settings
 
Back
Top