I was afraid that would be the problem. I didn't think it possible to
differentiate between an administrator saving a blank sheet vs a user
filling out and saving a sheet, however:
My only possible thought is that if in design mode as a template- when the
file has an unique template name and an extension of xlt instead of xls,
could you have a conditional formula in VBA that would allow you to modify
the file if the active filename is its original name (ie "template.xlt") but
not allow modifications or unprotect cells if the file name is no longer
"template.xlt"...........
I am pretty good at figuring out the logic path, but not the execution and
coding.... <G...... goes back to the old days of flowcharting programs
before writing them.. I am not savvy enough with VBA to know if there are
ways to achieve this, but without macros, I can do some amazing things with
formulas in spreadsheets. Just haven't had the available time to get macros
and VBA under my belt to where I am fluent enough with them.

Do you think that it is possible:

1: if macros are disabled- the main spreadsheet (form) would not be visible-
the only active worksheet says that the file must be loaded to allow macro
execution to work properly. This sets up all other things to work properly-
no access possible if macros are disabled.
Then:
2: in checking the current file name -
if the program loaded, matches the original filename (for
the blank template spreadsheet as the 'official copy'), then a few key
locked cells are unprotected, thus allowing them to be filled in, then
during any save operation, they are once again locked.

3: If a user loads a previously filled out form, it has a new
filename (that is a given the way these are archived), so key cells are
therefore protected so if the user tried to recycle it, the key fields are
locked, so its a futile attempt. (I only have to protect the customer name
and serial number field and that effectively stops this process dead in its
tracks because they load old customer records and forget to update other
fields-

With this logic, it allows the "administrator" of the template or form to
unlock the file, make modifications to it and resave it as a template, but
any user that would save it to disk with a serial number or customer name,
etc... would find the form unmodifiable in terms of changing one or two key
fields that are crucial to making it a unique form......so they must resort
back to the blank template to originate any new document- as it is not
possible to load an old document, and change the serial number (tracking
number) and other key information.

The reason- these people do not do a thorough job when tweaking an old
document, so wrong information is passed along, which creates tremendous
problems when this information passes through the organization- and the work
straightening it out is 1000-fold worse than the document originator just
taking the few extra minutes to fill out a blank form. One or two guys like
to recycle these forms, but don't update all the fields correctly, and hours
are wasted daily with the misinformation passed through the company. I'd
make them go back to a form filled out by hand, but handwriting is so
illegible that much time is wasted with that route as well. That brings me
to making the form a bit more idiot-proof as described above .

??
Thoughts?
How?

Thanks
Paul

"Tom Ogilvy" wrote in message
...
I don't see an easy solution to this. A macro can perform the actions,
but
if macros are disabled (as you state your problem is), then that wouldn't
be
a workable approach. Without macros, saving is saving - there is no
alteration of the worksheet included.

As easy as it is to save a copy of the workbook or worksheets, it seems
the
recalcitrant employees could easily make a copy of old forms and save them
as separate files under their own control.

I think a possible solution is to not use the form for the employees, but
have them put there information into a database and then generate the
forms
with a macro using the information in the database. In that way, the
information can be validated and if it is inconsistent or incomplete, it
could be flagged and returned to the employee for rework.

The interface to the database could be a worksheet or a Userform. Also,
it
may be appropriate to prime a new form with some of the old information -
this could be controlled through your interface.

--
Regards,
Tom Ogilvy

"PKyle" wrote in message
...
I have a worksheet designed as a paper form for the uesr to fill in with
information to print out and also save as a serialized record. An 8.5
by
11
"form" that has various locked cells and unlocked cells for data entry.
In
several worksheet cells, I have used VBA and macros to perform
operations
such as to replace the date with the date at time of saving the file.
As
indicated previously, the users of this spreadsheet have disabled macros
to:
1: disable the date/time stamp
2: bring up old previously filled out spreadsheets to slightly
modify
them, but in the process, missing key information fields (worksheet
cells)
that should be updated with relevant information.
The wake of chaos that is created

I'd normally go back to a xeroxed copy of a paper form, except that some
of
these individuals have such illegible handwriting, that neither option
appears attractive at present.

I wish to end the practices of back dating the spreadsheet and doing a
half-baked job of modifying a previously filled out excel worksheet and
all
the mistakes (by not starting with a blank template excel worksheet).

Hope this clarifies your question.

I guess hiding a form and revealing it whem macros are enabled is a
possibility- however- having a template such that can unprotect certain
cells, yet lock them up when the user goes to save the document appears
a
bit tricker at present.
Thanks
Paul

"Tom Ogilvy" wrote in message
...
Define what you mean by Form.

Do you mean a userform

Do you mean Data=Form from the menus

Do you mean a dialog sheet

do you mean a userform

or do you mean you have designed a worksheet to look like a paper
form.

--
Regards,
Tom Ogilvy

"PKyle" wrote in message
...
I have a form in excel with several worksheets that can vary. I
have
used
a
macro to update the date field when saving the file, so co-workers
cannot
"back-date" the date field. However, they have learned to disable
macros
when loading the spreadsheet. They also tend to recycle the forms
(load
a
previously filled out form to modify just a few fields, however, one
coworker typically does not do a thorough job and makes lots of
mistakes
which are quite costly to the organization.

If I could convert it to Access, I would, but this form requires a
lot
of
customization in attachments- which Access cannot accomodate.

I am wondering a few things as I have two problems to overcome:

1: I want to disable use of form if user chooses to disable macros-
one
or
two key fields are deactivated or protected if macros are disabled.
2: once the form is saved, I would like to lock a couple of key
fields
which essentially inactivates the form from being recycled and used
again-
the user has to go back to an empty template- to ensure the form is
filled
out correctly.


For a spreadsheet template:
Can I lock certain cells in the initial template form- so that
the
first
time the template is opened , the fields can be unlocked by a macro
so
the
user can fill them in, but as soon as the file is saved as a
worksheet,
these fields cannot be modified again- (ie serial number or case
number)
thereby forcing the user to consistently go to the template for a
new
instance of the form???

(and in using the template- if they bypass macros- these fields
stay
locked- so they cannot defeat the macro features such as actual
date/time
stamping and recycling old forms with new information??)

If this is possible- can someone help with the code and means by
which
I
can
accomplish this? I appreciate this!
Thanks
Paul