Thanks for the response Dave. I should mention a few details about my
workbook and if you don't mind tell me if it is possible to creat a
successful add-in.

This workbook is used as a quoting system for our sales team. We
manufacture all kinds of custom signage (sign cabinets, plastic faces,
channel letters, billboards, etc.). For each product I have built a custom
userform (15 Userforms) for the sales team to enter specifications of the
sign, then they click a button and the price is added to sheet1. Then the
userform is unloaded and all the userform control values are stored in
another worksheet. I have allocated one worksheet per userform. I do this
incase the salesman needs to edit that particular products userform instead
of starting from scratch. Still with me?

Here are my concerns:

I used your recommendation on deleting code from the workbook and determined
that the Userforms are the reason the file is so large. Basically the add-in
is a workbook that is loaded and remains hidden when Excel is opened, right?
If so, would it take a while for the workbook to open everytime?

Plus, some of the salesmen have 2007 and some have 2003, is that a problem
with an add-in?


--
Cheers,
Ryan


"Dave Peterson" wrote:

First, I don't think the macro code is the cause of the big size of your
workbooks. You could do a small test and remove all the code and save that
workbook to compare its size with the original.

But I do think that putting the code into each workbook is overkill--and a
monster to fix when there's a problem with one of the routines. Just finding
the workbooks that need to be fixed is a giant PITA.

And I don't think that I'd want your code in my personal.xl* file. First, you'd
have the same trouble (updating multiple files) if the code had to change.

Instead, I'd create a single addin that did the work. Then I'd either place
that addin in a common location (on a network drive that everyone can read) and
let the users install the addin.

If that's not possible (remote users--or users who are not always connected to
the network), then I'd distribute an addin with the instructions that it has to
be saved in the same folder on everyone's pc.

C:\WorkAddins\SalesUtils.xla

By using the same location, it'll make life much easier if workbooks are shared
between co-workers.

But by creating an addin, I'd have to give the users a way to execute those
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
or
http://spreadsheetpage.com/index.php...g_custom_menus

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

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

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

RyanH wrote:

I have a workbook that our sales team uses everyday. The workbook is 3MB and
is saved on our server. I put a shortcut on each of the salespersons
computer linking to the workbook. I force them to open the workbook as Read
Only. Everytime they save a quote it saves a 3MB file in a folder on our
server. I am worried of filling up the server. I think it would be best to
save all the macros contained in the workbook into there personal workbook,
thus saving alot of space.

My question is this, I update the master workbook on the server on a daily
basis. I want to give them access to those updates without having to go
around to everyones computer and updating the macros in their personal
workbook. Anybody have any ideas? I would entertain any ideas on this.

Thanks in advance,
--
Cheers,
Ryan

--

Dave Peterson