Distribute macro to users

[Diana]

I have written a macro for a spreadsheet, chicago.xls. Let's call the macro
testmacro.

The users would like to be able to run the same macro for other spreadsheets
(eg. denver.xls, dallas.xls, NewYork.xls, boston.xls, miami.xls etc..).

So they'll open the spreadsheet, press a button and get the desired result.

I have read some of the threads on putting a button on a floating toolbar.
Debra Dalgleish's info is helpful but I'm just getting very confused.

How can I give testmacro to the users so they can just press a button to run
the macro? I am using Excel Version 2002, SP3. Thanks.

 

[Otto Moehrbach]

Diane
If these "users" are Excel VBA knowledgeable, look at Chip Pearson's
site:
http://www.cpearson.com/excel/vbe.aspx
Look at the first 2 items:
Adding A Module To A Project
Adding A Procedure To A Module
Be sure to read the instructions about the needed references in each user's
Excel setup. Using these procedures, you can place a macro in a workbook,
set to run on opening of the workbook, that will insert a module and the
macro you want to give them, into their workbook, as well as create the
necessary button and macro assignment. You would create that workbook (the
workbook you send them) as well as the macro code in it.
That is a lot to chew.
One alternative would be for you to write a detailed procedure to give to
each user. The procedure would tell them how to create a module in their
workbook and copy/paste your macro into that module. It would also instruct
them on how to create a button on their worksheet and how to assign your
macro to that button.
Another alternative would be for each user to email you a copy of their
workbook and then you place the macro and button in their workbook and email
it back to them. HTH Otto

 

[Diana]

Hi Otto,

The users have no VBA knowledge.

I don't want the button to be associated with a particular workbook. The
idea is to allow the user to open ANY workbook of the format the macro is
designed for and it will work.

Thanks.