Interesting challenge

[PKyle]

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

 

[Tom Ogilvy]

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.

 

[PKyle]

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

 

[Frank Kabel]

Hi
to get you started some ideas:
1. Preventing the user from disabling macros:
- Create an additional sheet which just tells the user he has to enable
macros to make this file work. Design it according to your needs
- Hide all other sheets (maybe with xlSheetVeryHidden) directly in VBA
code
- protect the sheets AND the workbook (maybe with an additional
password
- now use the workbook_open event of this file to
-> hide this initial sheet
-> unhide all other sheets
-> doing this would require to unprotect the workbook at the
beginning and protect it again at the end
something linke the following in your workbook module:
sub workbook_open()
me.unprotect password:="your_password"
me.worksheets("entry sheet").visible=true
me.worksheets("welcome").visible=xlsheetveryhidden
me.protect password:="your_password"
end sub
- Also use the workbook_close event of your workbook to
-> hide the sheets again
-> unhide the welcome sheet
-> save the workbook with these settings
Something like
sub workbook_BeforeClose()
me.unprotect password:="your_password"
me.worksheets("entry sheet").visible=xlsheetveryhidden
me.worksheets("welcome").visible=True
application.enableevents=false
me.save
application.enableevents=True
me.protect password:="your_password"
end sub
- adapt this to your requirements. For event procedures see:
http://www.cpearson.com/excel/events.htm


2. Lock cells:
- use the workbook_beforesave event for this
-> check if the cells are already filled
-> unprotect this worksheet
-> change the locked status of these cells
-> protect the worksheet again
- adapt this to your needs

Note: A real determined user could also overcome this. To make it a
little bit more difficult for him:
- protect your VBA project
- use passwords
- 'punish' them if they still try to avoid these procedures

 

[onedaywhen]

Also use the workbook_close event of your workbook to

- hide the sheets again
- unhide the welcome sheet
- save the workbook with these settings

This would allow the crafty user (takes one to know one) to open the
workbook with macros enabled (to allow the protection code to run),
save but don't close it and crash Excel to ensure the Workbook_Close
event (i.e. protection code) does not run. Result is, if they then
open the workbook a second time with macros disabled, they will have
an unprotected workbook. Tant va la cruche à l'eau qu'à la fin elle se
brise.

Better to do the above process in the Workbook_BeforeSave event,
remembering to handle the case where the event fires but the user
cancels the save. Force a ThisWorkbook.Save in Workbook_Close. Also
handle the case where the workbook is saved but not closed i.e. run
the Workbook_Open code again. La fin justifie les moyens.

--

 

[Tom Ogilvy]

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.

 

[Tom Ogilvy]

Why not just open the workbook with macros enabled, select all or the
appropriate sheets and copy them to a new workbook. Use these sheets at
will.

 

[PKyle]

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

 

[PKyle]

All interesting stuff. My previous message talked about looking at the
filename and determining whether to unlock protected fields... I think this
can overcome some of the copy to new workbook issue?? The new workbook will
not have the same filename as the original template

(most of these guys are not savvy enough to know the copy sheet to new
workbook to disable protection stuff.... and just barely figured out the
disable macro thing to defeat the date stamp during file save.). These are
people who cannot figure out formulas in excel, let along macros- 90% of
their excel skills are around work processing and drawing boxes around
fields, very little formula stuff. They mainly figured out the macro thing
because they thought they had a virus, so turned it off and noticed the
time/stamp went away, so continued doing it that way.

Paul