Excel macro question

[bramnizzle]

Okay...I created a handy utility for the people at my work. It
contains macros and cell formulas. I work in the engineering industry
and I have multiple projects to keep up with, which includes multiple
dates and bits of information per project to keep up with. So, I
developed a handy excel file that would allow users to input some
information and macros would create sheets, sort information, create
hyperlinks to sheets, etc., etc. I developed a working copy and mass
emailed the file to all of my co-workers. It's a useful tool for us
who have 20+ projects to keep up with. Here's my dilemma...I found a
bust in one line of my one of my macros and there is a sheet I wanted
to change some formatting for. I can change it on mine no problem,
but to have EVERYONE I gave the copy to change this information would
be a nightmare because most of them are afraid of excel to begin
with. So, I'm looking for something similar to a "software update".
I don't want to give everyone my new copy of the spreadsheet because
now ALL of that information they input will be useless because now
they'll have to type in all of that information into the new
spreadsheet. Basically, I want an add-in or a macro that I can send
to everyone that once they open or run it will call the Project
Management utility spreadsheet, edit a few lines in a macro that is
built into that file, reformat a few cells, and then save...all
without losing any information that was already input. Am I making
this clear? Can anyone help?

In a nutshell...

I'm looking for a way to create either an add-in or a macro that I can
run that would call up a specific worksheet within a workbook, edit a
few lines in an existing macro, reformat a few cells, and then
save...all this without overwriting and losing any data that was
previously entered.

Thanks in advance.

 

[Tom Ogilvy]

Might be time to bite the bullet now and do it the right way.

http://www.jkp-ads.com/Articles/DistributeMacro00.htm

You should separate the code from the data. Move all your code to an addin
(make alterations to account for this location).

then send out the addin. In the code, treat the existing workbooks as data
books.

What you will be able to do will depend on what the trust and security
settings are for your "customers"

You can see how to manipulate code and the vbe from code at
http://www.cpearson.com/excel/vbe.htm

So along with all the original code, you can add code to recreate the data
workbooks. Copy the data to a new workbook, delete the old and rename the
new. This is the easiest way to "remove" the existing code.

You should always assume there will be changes. That is why I suggest
making the big change now.

If the users all have access to a common drive, it might be best to have
them all access the same copy of the addin.

the users will have to choose to load your addin.