basic template questions (on a network drive)

[KR]

I currently manage a network folder which contains an Excel file for each of
about 250 users. In the current incarnation, each workbook is completely
separate and has it's own userforms and code (several modules). I've found
is that managing small changes in the code is a nightmare, because each
workbook requires an update. In addition, I'm not always notified as new
users are assigned, and I generally find out because someone complains that
they don't have a workbook. So, I'm thinking about re-building everything
with a different design, but I haven't done a lot with template files so I'm
not sure how well they'd work for the following.

I'd like to store all the code (and userforms?) in one main file, linked to
each user file. Ideally, I could give everyone a (read-only) link to the
main (template?) file, and have the code in that file check their userID,
and open their individual file, and then close/hide their read-only copy of
the main file. If the user doesn't have an existing file based on their
userID, I can create one on the fly.

Then when the user clicks on the command button in the file, I'd like it to
pull the userforms and code from the main (template?) file- that way, if
updates are needed, I'll only have to update the one main file.

Is this a reasonable approach? Any caveats? I don't have access to each
person's PC (geographical distribution of users) so I can't change their
settings or add anything to their individual PCs, which are locked down by
IT anyway. I'm looking for the best standalone solution that I can implement
on the network drive.

Any examples of how to best link the individual files to reference the
userforms and code in the main file would be appreciated!

Thanks,
Keith

 

[Zone]

You should definitely keep your code and userforms in one "master"
workbook file so you can maintain them in an orderly way. Here is an
easy way to do it, which works well if you're always going to get
things going by calling the same procedure:
1. Put the "master" file (which contains the code) on the network
drive.
2. Open both the "master" code file and the file your users will be
working with.
3. Use the Forms menu to put a button on the file the user will be
working with.
4. Assign the button to the macro in the "master" workbook.
5. Close and save both files.
This works well because after Excel opens the "master" workbook (which
contains the code), it reactivates the workbook that was on the screen
before (the user's workbook). Therefore, the user's worksheet remains
active. If your version of Excel doesn't exhibit this behavoir, you'll
have problems. Also, you cannot activate sheets in your "master"
workbook, or the user's file will no longer be active. I name the
sheets in the "master" something other than "Sheet1", "Sheet2", etc.,
to avoid confusion. Lastly, you should close the "master" workbook
when the routine is finished, so it doesn't remain open on the user's
machine. If many users may be using the "master" at one time, you may
have to set its sharing options. If possible, make it read-only. I
hope this works for you because it's such a simple way to do it.
James

 

[KR]

Here's another idea that a co-worker just came up with- what if I keep the
code modules separate (as .bas and whatever userforms export as) in the same
drive, then each time a user opens their file, it imports the appropriate
modules- so the only code in each workbook would be in the onopen event
(load all bas files) and the close event (remove all bas modules).

I've had trouble trying to /replace/ .bas files within a macro, e.g. have an
external macro remove a .bas from a file, then add the updated copy of the
same module...even when I put in time delays, save the file, etc., it isn't
reliable. It often simply doesn't remove the old .bas file. However, if this
worked properly, then it wouldn't ever have to remove the module (if it
actually removed properly during the previous onclose event).

Anyone have any thoughts on this method, as opposed to keeping all the code
in a separate workbook? With the separate workbook option, I'd need to hide
the workbook so the user didn't switch windows and close it, then search for
it and close it when they close their individual workbook... I'm worried
about the logisitical issues of doing it that way too....

Thanks for any advice,
Keith

You should definitely keep your code and userforms in one "master"
workbook file so you can maintain them in an orderly way. Here is an
easy way to do it, which works well if you're always going to get
things going by calling the same procedure:
1. Put the "master" file (which contains the code) on the network
drive.
2. Open both the "master" code file and the file your users will be
working with.
3. Use the Forms menu to put a button on the file the user will be
working with.
4. Assign the button to the macro in the "master" workbook.
5. Close and save both files.
This works well because after Excel opens the "master" workbook (which
contains the code), it reactivates the workbook that was on the screen
before (the user's workbook). Therefore, the user's worksheet remains
active. If your version of Excel doesn't exhibit this behavoir, you'll
have problems. Also, you cannot activate sheets in your "master"
workbook, or the user's file will no longer be active. I name the
sheets in the "master" something other than "Sheet1", "Sheet2", etc.,
to avoid confusion. Lastly, you should close the "master" workbook
when the routine is finished, so it doesn't remain open on the user's
machine. If many users may be using the "master" at one time, you may
have to set its sharing options. If possible, make it read-only. I
hope this works for you because it's such a simple way to do it.
James

 

[ch]

You should definitely keep your code and userforms in one "master"
workbook file so you can maintain them in an orderly way. Here is

Yes speaking from experience this works well and is easy to maintain.

We open one master workbook from the server, that has nothing on it but
our company custom toolbar/macros + then everything is done from the
toolbar.

A calendar, in/out list, appt list are the only things on the sheet so
everyone has to open this first thing for day to day operations anyways
and by doing that they get the toolbar.