Macro -->shared.xls --> .xla add-in --> now not in macro list

[FUBARinSFO]

Hi:

I want to make a macro available to all workbooks. Placing it in
xlstart doesn't work because it's not loaded if you open an existing
workbook xls file, only when you "start" Excel 2003 (clean workbook).

So I placed the code in a blank workbook, saved it as an add-in .xla
in the add-ins folder, and now it appears available as an add-in to
all workbooks, including specifically existing workbooks.

Only problem is, it's not listed in the Tools | Macros list anymore,
so now how do I invoke it? It's not a function, it's a sub that
performs string conversions on the currently selected range.

Thanks in advance for your help.

-- Roy Zider

Windows XP SP3, Microsoft Office Pro Excel 2003 SP3

 

[Dave Peterson]

I keep my personal.xla (an addin) in my XLStart folder (and I use xl2003).

If I start excel by clicking on the excel icon on my desktop, the addin is
loaded.

If I start excel by doubleclicking on a .xls file, the addin is loaded.

The only way I can avoid loading the addin is by starting in safe mode (or
automation mode).

This personal.xla addin located in XLStart doesn't appear in the Tools|Addins,
though.

And any addin won't show up in that tools|macro dialog. You can still type in
the full name of the sub to run, but that usually isn't feasible.

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

 

[FUBARinSFO]

I keep my personal.xla (an addin) in my XLStart folder (and I use xl2003)..

If I start excel by clicking on the excel icon on my desktop, the addin is
loaded.

If I start excel by doubleclicking on a .xls file, the addin is loaded.

The only way I can avoid loading the addin is by starting in safe mode (or
automation mode).  

This personal.xla addin located in XLStart doesn't appear in the Tools|Addins,
though.

And any addin won't show up in that tools|macro dialog.  You can still type in
the full name of the sub to run, but that usually isn't feasible.

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.htmhttp://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an
addin)
orhttp://www.rondebruin.nl/2007addin.htm












--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dave:

Thank you for your links, which I've saved for future reference. I
was trying to avoid adding menu bars or other VBA-code dependent
solutions, and wanted at most a button on an existing toolbar to hook
into the macro.

But it turns out the system was working correctly after all (or at
least it is now). I've got a personal.xls (name is important, case is
not) which contains the macro I wanted to be available to all
workbooks. I've put it in the xlstart folder at C:\Documents and
Settings\Administrator\Application Data\Microsoft\Excel\XLSTART.

The macros in personal.xls are loaded whether I click on an
existing .xls file, or I invoke Excel from the Start menu, the module
for personal.xls is listed in the VBA Project window, and most
importantly is available from Tools | Macro. No nag dialog is
presented asking if I want to disable the macros, which is also what I
wanted.

Now, I realize this is the way it was supposed to work all along, but
for some reason it didn't work this way yesterday night, which is why
I resorted to the .xla add-in. Maybe it was "user error", but we shall
see.

(For further diagnosis, another workbook, BOOK.XLS, is also located
in C:\Documents and Settings\Administrator\Application Data\Microsoft
\Excel\XLSTART and is NOT loading, irrespective of whether I click an
existing .xls file or invoke Excel from the Start menu. Tools|Options|
General "At startup, open all files in" text box is empty, so it
should be defaulting to this default location, I think. But who knows?

And when I start Excel from the Start menu, it opens personal.xls now
rather than book1.xls as it did before. This is getting too confusing
for me.)

-- Roy

 

[FUBARinSFO]

Dave:

Thank you for your links, which I've saved for future reference.  I
was trying to avoid adding menu bars or other VBA-code dependent
solutions, and wanted at most a button on an existing toolbar to hook
into the macro.

But it turns out the system was working correctly after all (or at
least it is now).  I've got a personal.xls (name is important, case is
not) which contains the macro I wanted to be available to all
workbooks.  I've put it in the xlstart folder at C:\Documents and
Settings\Administrator\Application Data\Microsoft\Excel\XLSTART.

The macros in personal.xls are loaded whether I click on an
existing .xls file, or I invoke Excel from the Start menu, the module
for personal.xls is listed in the VBA Project window, and most
importantly is available from Tools | Macro.  No nag dialog is
presented asking if I want to disable the macros, which is also what I
wanted.

Now, I realize this is the way it was supposed to work all along, but
for some reason it didn't work this way yesterday night, which is why
I resorted to the .xla add-in. Maybe it was "user error", but we shall
see.

(For further diagnosis, another workbook, BOOK.XLS, is also located
in  C:\Documents and Settings\Administrator\Application Data\Microsoft
\Excel\XLSTART and is NOT loading, irrespective of whether I click an
existing .xls file or invoke Excel from the Start menu.  Tools|Options|
General "At startup, open all files in" text box is empty, so it
should be defaulting to this default location, I think. But who knows?

And when I start Excel from the Start menu, it opens personal.xls now
rather than book1.xls as it did before.  This is getting too confusing
for me.)

-- Roy- Hide quoted text -

- Show quoted text -

Further to this earlier note, it turns out that personal.xls is not
hidden but is a visible open workbook now. Perhaps this is the
problem I had last night -- I didn't want the workbook to be visible
and listed in Task manager and an icon to cycle through, which led me
to the add-in route. It was late, can't remember. When personal.xls
is closed, then the macros leave with it. So still not optimal.

Roy

 

[Dave Peterson]

There is a difference between a .xls (normal workbook) and .xla (addin). The
macros that are in personal.xls will be shown in the tools|macro dialog (unless
you do something special and make them private).

But you shouldn't have a book.xls in that XLStart folder. You want a workbook
template (save it as a template and use an extension of .xlt). The filename
should be book.xlt.

If you have a sheet template (new sheets added to existing workbooks), then this
filename should be sheet.xlt and it should be saved as a template.

I am surprised that you're using the Administrator branch to hold these files.
I use the (normal) user account.

C:\Documents and Settings\(username)\Application Data\Microsoft\Excel\XLSTART

But maybe you always logon as the Admin????

========
Personally, I like using the personal.xla (the addin version). I like the
menumaker technique that John Walkenbach shares. I think it looks very nice and
is easier to use.

And I like that if I have user defined functions in my addin, I can use:

=myfunc(a1,a3,c9)
Just like if the function were built into excel itself

If I used a normal workbook (personal.xls), I'd have to use something like:
=personal.xls!myfunc(a1,a3,c9)

======

For what it's worth, I have 3 files in my XLStart folder:
Personal.xla
Sheet.xlt
book.xlt

After you create book.xlt and delete book.xls (or move it elsewhere if you're
concerned), what files do you have in your XLStart folder?

 

[FUBARinSFO]

There is a difference between a .xls (normal workbook) and .xla (addin).  The
macros that are in personal.xls will be shown in the tools|macro dialog (unless
you do something special and make them private).

But you shouldn't have a book.xls in that XLStart folder.  You want a workbook
template (save it as a template and use an extension of .xlt).  The filename
should be book.xlt.

If you have a sheet template (new sheets added to existing workbooks), then this
filename should be sheet.xlt and it should be saved as a template.

I am surprised that you're using the Administrator branch to hold these files.
I use the (normal) user account.

C:\Documents and Settings\(username)\Application Data\Microsoft\Excel\XLSTART

But maybe you always logon as the Admin????

========
Personally, I like using the personal.xla (the addin version).  I like the
menumaker technique that John Walkenbach shares.  I think it looks verynice and
is easier to use.

And I like that if I have user defined functions in my addin, I can use:

=myfunc(a1,a3,c9)
Just like if the function were built into excel itself

If I used a normal workbook (personal.xls), I'd have to use something like:
=personal.xls!myfunc(a1,a3,c9)

======

For what it's worth, I have 3 files in my XLStart folder:
Personal.xla
Sheet.xlt
book.xlt

After you create book.xlt and delete book.xls (or move it elsewhere if you're
concerned), what files do you have in your XLStart folder?







FUBARinSFO wrote:










--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dave:

Thanks for your comments.

I had BOOK.XLS in my XLSTART folder because I was trying to solve the
problem of common access to macros, and I thought that possibly a) the
"magic" name for the file had changed from personal.xls to BOOK.XLS
between versions of Excel, or that b) it would load because all files
in xlstart are supposed to be loaded at start (or opened when
selected), which was my understanding.

I don't have special workbook or sheet templates, as I try to leave my
system as simple as possible, but no simpler.

I do in fact log in as Administrator, since I can't get any work done
otherwise. This is as much a testing and development system as it is
for production, and loading and changing programs often requires
adminstrator access. I should probably change it to another login
name leaving it in the Administrators group but my imagination has
failed me coming up with anything other than "admin". Perhaps you can
suggest an alternative login name that is easily rememberd, typed but
yet hard to crack. aroundthebowlanddownthehole might qualify.

-- Roy

 

[Dave Peterson]

If you have to logon as the admin, I'd use that name.

 

[Dave Peterson]

To hide the personal.xls workbook.

Open excel.
With personal.xls the active workbook, Window|Hide
Then close excel.

You'll be asked if you want to save personal.xls. Say yes.

Reopen excel to test.