Macros Available without hiding 'personal.xls'?

[Shelly]

Hello, Is it possible to make macros available to all workbooks without
needing to hide (or show) 'personal.xls'?

I have a worksheet (provided by my company that I need to use frequently)
that can only run if it is the only worksheet or workbook open. So, if
'personal.xls' is always opened when I start excel I have to close it before
I can calculate my worksheet provided by my company. But, if I don't have
'personal.xls' open each time I start excel I don't have access to the macros
I have created.

Any ideas on how to get around this and have macros available even this
'personal.xls' not open?

Thanks!

 

[Dave Peterson]

I think I'd go back to the developer (or developer's management) and explain the
problem.

Maybe they'll see the error of their ways????

======
If you can get to their code, maybe you can modify it to stop checking.

======
Maybe their counting workbooks, like in:
msgbox workbooks.count

If you save your personal.xls as an addin, you may be able to avoid the
problem. But your macros won't appear in the tools|macro|macros dialog.

So you'll need to give yourself an easy way to run your macros.

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

In xl2007, those toolbars and menu modifications will show up under the addins.

And if you use xl2007:

If you want to learn about modifying the ribbon, you can start at Ron de Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an
addin)
or
http://www.rondebruin.nl/2007addin.htm

========
You could always go into the VBE and select your procedure and hit F5, but that
would be a pain.

 

[JLatham]

Check with your IT/IS department before attempting either of these possible
solutions, since somehow I suspect we are about to mess around with things
and go against company policies that might just get you into some deeper
waters than you care to be in.

First possible method: open 'personal.xls' AFTER you've opened the company
workbook? It may be that the company workbook only checks for other books
being open when it starts up, and not after it does an initial test to see if
it is all alone in the current instance of Excel.

Second possible method: Go into personal.xls and use [Alt]+[F11] to view
the VB Project in it (this takes you into the VB Editor). Go into the
modules and simply copy and paste all the code from them into a .txt file and
save the .txt file. Later when you open the company workbook, start off by
again using [Alt]+[F11] to get into the VB Editor and use Insert | Module
from its menu to create a new, empty code module. Open up your .txt file and
copy all of the code in it and paste it into the new, empty code module
presented to you earlier.

At this point I'd save the workbook to your local system. Continue using
the workbook until you've finished with it. Now you have to hide all of the
traces of your nefarious deeds!! Again go to the VB Editor via [Alt]+[F11]
and Right-Click on the code module that you created and select "Remove ..."
from the list (usually it has the name of the module as Remove Module1...).
Respond [NO] to the offer to export before removing. Poof, it will be
removed from the file and now you can save the file back to the corporate
area.

I can think of several reasons this second method might not work, not the
least of which is that it may be a shared workbook, so others may use the
real one while you're using the copy and when you save your copy back out
then everyone else's work done during the same period goes bye-bye!! And
there you are, in that pool of deep, hot water I was talking about earlier.

 

[Gord Dibben]

There should be no reason why an open Personal.xls should stop you from
calculating the workbook you have open.

Unless you have some type of event code in the workbook that would prevent
it from opening if another workbook is already opened.

If that's the case, maybe move your macros into a new workbook and save that
as an Add-in

Or find out why you aren't allowed to have that particular workbook open
with another.



Gord Dibben MS Excel MVP