PASSWORD PROTECT BEFORECLOSE

[KandK]

Tristan was kind enough to enlighten me into using Visual Basic Editor to
have a message box asking Are you sure you want to close? when someone tries
to close a workbook. However it would be great if I could password protect
the closing of a workbook. This is the first time I have used Visual Basic
Editor so with my very limited experience and Microsoft Excel Help I could
not work out a way. So if anyone has any suggestions or could even tell me it
is not possible it would be greatly appreciated. Many thanks.

[Tom Ogilvy]

This sounds like a real bad idea.

If you don't want them to save the workbook, then make it read only. If you
want to prevent saving to a different name, then in the beforeclose event,
set Cancel = true and close the workbook.

Any macro based protection is easily defeated by disabling macros, however.

--
Regards,
Tom Ogilvy


"KandK" <[email protected]> wrote in message
news:[email protected]...
> Tristan was kind enough to enlighten me into using Visual Basic Editor to
> have a message box asking Are you sure you want to close? when someone
tries
> to close a workbook. However it would be great if I could password protect
> the closing of a workbook. This is the first time I have used Visual Basic
> Editor so with my very limited experience and Microsoft Excel Help I could
> not work out a way. So if anyone has any suggestions or could even tell me
it
> is not possible it would be greatly appreciated. Many thanks.

[KandK]

What I want to do is give the utmost ptotection to stopping someone closing a
workbook by accident. The workbook in question is going to be viewed on a
different monitor maybe in a position not viewable to the computer operator
so they may not be aware they have closed it. The computer in question is in
an office where it is used by many different people with varying degrees of
computer knowledge. although they would not do it on purpose it is possible
someone may shut it by mistake. Thanks for your advice anyway.

"Tom Ogilvy" wrote:

> This sounds like a real bad idea.
>
> If you don't want them to save the workbook, then make it read only. If you
> want to prevent saving to a different name, then in the beforeclose event,
> set Cancel = true and close the workbook.
>
> Any macro based protection is easily defeated by disabling macros, however.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "KandK" <[email protected]> wrote in message
> news:[email protected]...
> > Tristan was kind enough to enlighten me into using Visual Basic Editor to
> > have a message box asking Are you sure you want to close? when someone
> tries
> > to close a workbook. However it would be great if I could password protect
> > the closing of a workbook. This is the first time I have used Visual Basic
> > Editor so with my very limited experience and Microsoft Excel Help I could
> > not work out a way. So if anyone has any suggestions or could even tell me
> it
> > is not possible it would be greatly appreciated. Many thanks.
>
>
>

I successfully completed a project like this last year- folks were
recycling forms- and not filling them out properly- which was creating
chaos in the organization. The sheets were forms were protected with some
cells unprotected that needed to be filled out. As users would 'recycle
them"- (open up an old form to 're-use' previously filled out info- they
were not too careful about completing it properly- so wrong information and
wrong instructions were conveyed, which resulted in a lot of wasted time by
the people who received the forms- all for the sake of a few people who
thought they'd save 2-3 minutes by re-using a previously filled-out-form.
The mistakes were easily 200-1 in cost/time. 2-3 minutes saved at the
clerical level led to extra expenditures that were hundreds of times more
costly and thousands in terms of delays.

Basically I had to write the routine to unlock some of the cells in the form
when opened, time stamp it when saved, but lock down as when saved. Ahh-
but this does not take care of the macro-defeater.

The best part was defeating the approach of disabling macros-
here's how I nailed that one:
Hide and password protect all the worksheets with one sheet left visible
that says that macros have to be enabled to view and use the sheet. As this
macro is executed upon opening, it hides this sheet and shows all the active
forms. The form cannot be loaded unless macros are enabled.

(Note- the average user that was 'recycling forms' did not have enough
savvy to use a password buster to break into the spreadsheet- otherwise I
would have used an Access database fronted into Visual Basic to create the
form instead of Excel.)

It was a lot of fun and a PITA (pain in the A##) all at the same time

let me know if you are interested and I can share the details.

Paul

..
"KandK" <[email protected]> wrote in message
news:[email protected]...
> Tristan was kind enough to enlighten me into using Visual Basic Editor to
> have a message box asking Are you sure you want to close? when someone
> tries
> to close a workbook. However it would be great if I could password protect
> the closing of a workbook. This is the first time I have used Visual Basic
> Editor so with my very limited experience and Microsoft Excel Help I could
> not work out a way. So if anyone has any suggestions or could even tell me
> it
> is not possible it would be greatly appreciated. Many thanks.

[KandK]

Paul

Thanks for your reply that was really useful. The project I am working on is
still very much "In Progress". If you do not mind can I put your offer on
hold and if I encounter similar problems get in contact for the details.Once
again many thanks for responding I really do appreciate the time that people
spend giving help to people like myself.

Keith

"[email protected]" wrote:

> I successfully completed a project like this last year- folks were
> recycling forms- and not filling them out properly- which was creating
> chaos in the organization. The sheets were forms were protected with some
> cells unprotected that needed to be filled out. As users would 'recycle
> them"- (open up an old form to 're-use' previously filled out info- they
> were not too careful about completing it properly- so wrong information and
> wrong instructions were conveyed, which resulted in a lot of wasted time by
> the people who received the forms- all for the sake of a few people who
> thought they'd save 2-3 minutes by re-using a previously filled-out-form.
> The mistakes were easily 200-1 in cost/time. 2-3 minutes saved at the
> clerical level led to extra expenditures that were hundreds of times more
> costly and thousands in terms of delays.
>
> Basically I had to write the routine to unlock some of the cells in the form
> when opened, time stamp it when saved, but lock down as when saved. Ahh-
> but this does not take care of the macro-defeater.
>
> The best part was defeating the approach of disabling macros-
> here's how I nailed that one:
> Hide and password protect all the worksheets with one sheet left visible
> that says that macros have to be enabled to view and use the sheet. As this
> macro is executed upon opening, it hides this sheet and shows all the active
> forms. The form cannot be loaded unless macros are enabled.
>
> (Note- the average user that was 'recycling forms' did not have enough
> savvy to use a password buster to break into the spreadsheet- otherwise I
> would have used an Access database fronted into Visual Basic to create the
> form instead of Excel.)
>
> It was a lot of fun and a PITA (pain in the A##) all at the same time
>
> let me know if you are interested and I can share the details.
>
> Paul
>
> ..
> "KandK" <[email protected]> wrote in message
> news:[email protected]...
> > Tristan was kind enough to enlighten me into using Visual Basic Editor to
> > have a message box asking Are you sure you want to close? when someone
> > tries
> > to close a workbook. However it would be great if I could password protect
> > the closing of a workbook. This is the first time I have used Visual Basic
> > Editor so with my very limited experience and Microsoft Excel Help I could
> > not work out a way. So if anyone has any suggestions or could even tell me
> > it
> > is not possible it would be greatly appreciated. Many thanks.
>
>
>