Linking Modules to Excel

[Kinl]

I have this 'Monthly Review Sheet' that each one of the employees at my company uses. They all have there own document for themselves, this is what the manager updates. Basically standards and goals are also tracked on this document. I have built many functions into this document, but when a change happens, I dont want to go into 200 documents throughout our network to change the contents of each of the documents modules.
Is there anyway I can link a module, to an excel spreadsheet? So then I can make one change and it will affect all spreadsheets?

Thanx guys/gals,
donn

 

[segmentationfault]

Move your code into a workbook that is blank except for the VBA modules. You can refer to the monthly review books from within your code, and making since the code exists only once (but works on all files) you only need to change it once.

 

[MichaelRed]

Being a Anti-Fan of Excel, my 'suggestion' needs to viewed with the proverbial grain of salt.

200 (or so) instances of anything becomes a chore to track. Excel spreadsheets (especially multiple copies) are a good example of the reason relational databases are so widely used today. Another 'feature' of Excel is it's flexability, as in the fact that anyone can EASILY add or modify 'procedures', move information around, add objects )additional wordsheets) ...

My suggestion wiould be to migrate the whole process to a relational database, where you generate ONE set of forms, reports, tables, nodules, and queries for the users. Make a "Master" db where you do all of the "program development" and then send copies to each of the host of hordes of employees. Set up a routine in the process which - each time it starts, copies any new/updated objects from the master to the copy. You COULD eve maintain a single network copy of the front end with a single set of tables. As long as the process included security, the log-in could identify the user and that couold be set up to have the process only look at the individual's info.

One program. One set of data (tables). Many users. Each user sees ONLY his data.



MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[Kinl]

I do agree w/ the database, and that was my original suggestion to this company. But they are reluctant to let us build one. For who knows what reason, but they are reluctant. I'm also a 'ANTI-FAN' of excel, but this is one time that I have to work with the problem. I do appreciate your thought of the database though. That is the overall plan, I'm still trying to convince management to let me build one for them, but ..... who knows if that will happen. THis company is very stubborn.

As for a blank workbook, how would I make all the other files reference that one workbooks module?

Thanx a million.
donn

 

[MichaelRed]

Hmmmmmmmmmmmmmmm,

Immovable object. Irrestable Force.

Hmmmmmmmmmmmmmmm,

Show the above to the Corporate guru of non-db fame.


About the Macro reference, I lost it. Haven't "lost" that battle since going to 2K and it has 'wandered'. Used to be under tool. Set a reference to a macro sheet. Can't find it any more. Also gone from help. Peerhaps "segmentationfault" can overcome their "faults" long enough to provide that part.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[Store300]

You could build the functions into a self installing excel addin which you could then email to your users. Store300

Store300@ftnetwork.com

 

[segmentationfault]

Overcome my faults? Never! I would have read this sooner but I was busy dereferencing the null pointer.

It seems I'm quite out of my realm on this question, however, since it was mentioned, my best stab at a solution would be thus:

(and admittedly I'm inexperienced with this type of situation)

Make a template for your user files. Include in the auto_run a call to open (readonly) the workbook with all the specific macros. This assumes that networking is provided...

Within the template, in every place that a macro is required, simply refer to a named macro in the shared workbook. For example, in the template have a macro that is run when Cell(1,1) changes, but this macro would simply point to a sub in the shared workbook.

When changes to the code need to be made, do it all in the shared workbook, and everybody is immediately brought up to speed. I apologize if this is overly simplistic or obviously flawed, but it seemed to meet the requirements originally posted without being very complicated at all.

Just trying to keep it simple.