macros for end users

[lsulky]

I need to make two macros readily available to one primary user, who
will then create a variety of workbooks with those macros attached and
pass them along to end users. I'm trying to avoid having either the
primary user or the end users go through any code installation
routines; they should be able just to use the macros. But everything
I've read either assumes that I'm writing macros only for myself or
that I'm distributing them to technically savvy users.

I tried to put the macros on toolbar buttons, but there were problems,
so then I tried keyboard shortcuts, which got me closer, but there are
still problems. One user can use the keyboard shortcuts successfully,
another can only use one of them, one of the macros shows up with an
invalid name in the macro list but still runs from the keyboard....etc.
All kinds of weird stuff.

I'm trying to do things like I would in MS Word, and it's just not
working. I need the "right" way to do this so I can feel confident that
by the time the real end users get their hands on the workbooks, these
macros will work for them. Should I do these as add-ins? Do the users
have to do anything techie at all to access them, or can I automate
that for them?

Thanks ----

 

[lsulky]

A bit more detail about the "weird stuff".

I created my macros within ThisWorkbook. But now, in my macro list
(Tools > Macro > Macros), they show up with these names:
protedit.xls!ProtDelete and protedit.xls!ProtInsert. (protedit.xls is,
obviously, the name of the workbook.) Neither macro is runnable from
this window.

If I close the macro window and just press my keyboard shortcut for
ProtInsert, I just get the keyboard error "ding". But if I open the
macro window again, the macro names now show as ThisWorkbook.ProtInsert
and ThisWorkbook.ProtDelete. Then if I close the window and press the
keyboard shortcut again, it operates properly. And if I open the macro
window yet again, it shows the other macro name correctly, but the name
of the macro I just executed is wrong again. After a few more confusing
iterations, it seems to all clear up... for _me_. But not for my
primary user, who can't get the first macro to run at all.

Any ideas on these specifics or on my more general earlier question
would be greatly appreciated!

 

[lsulky]

Anybody out there? There must be a way. How does everybody else make
macros available to non-tech end users without requiring a distinct
installation process? Please help!

 

[Dave Peterson]

If I want to add options to the menubar, I use a variation of John Walkenbach's
menumaker:
http://j-walk.com/ss/excel/tips/tip53.htm

If I want to add a toolbar of my own, here's how I do it:
http://groups.google.co.uk/[email protected]

I save the file as a .xla (addin).

Then I tell the end user to just open that workbork when they need that
functionality.

If it's a set of routines that should always be available, the user can add it
manually via Tools|Addins (and browse for their copy -- or point to a common
network file.

If there has to be separate installs (gonna be a pain to play catchup when you
have to make a change), Jan Karel Pieterse has a nice install program included
in the .zip file for his (with Charles Williams and Matthew Henson) name manager
utility.

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp/