Jim Thomlinson wrote:
To capture the password you want to use a user form with a text box and a
command button on it... The text box can be formatted with the property

PasswordChar *

This form will hold the passwords for each sheet. Based on the sheet that
the user is trying to unlock the password form will just set the global
variable to true (if the password matches).

One thing I did not mention in my original post is to roll back teh change
that the user made if the password does not pass the test.

Application.Undo

What you are trying to do here is not simple and it will take a bunch of
fiddling around.
--
HTH...

Jim Thomlinson


It surprises me that it's not all that simple, thought this one would
be. But you've given me a good starting point and I'm working on it
now. Will probably be asking more questions as I write the code.

Thanks to everyone for their help.


"davegb" wrote:


Jim Thomlinson wrote:
That is an interesting problem... Lets start with should you use the sheet
level of the workbook level change event.

The sheet level event reacts to changes on the sheet that the code is in.
The workbook level event reacts to changes in any sheet in the book. So in
this case (since we want to look at changes on any sheet to trigger the
password code) we should probably use the workbook code. Otherwise we have to
copy the same code into 10 different sheets. There are a couple of sheets
that we don't want to react to the code though so we have to write something
to exclude those sheets.

My general approach would be to leave the sheets unprotected from data entry
and handle that with code. We also need to know if a password has been
entered for the sheet we are on. To that end we will need a global variable
to track whether the password has been entered. Flip it to true when the
password is entered and back off again when the user switches sheets (just
one possibility).

This should give you some ideas I hope...

Public PASSWORD_ENTERED As Boolean

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Select Case Sh.CodeName
Case "Sheet1", "Sheet2" 'Excluded sheets
Case Else
If PASSWORD_ENTERED = False Then
Call EnterPassword(Sh)
End If
End Select
End Sub

Sub EnterPassword(ByVal Sh As Worksheet)
Select Case Sh.CodeName
Case "Sheet3"
'Prompt for Valid Sheet 3 PassWord
'Flip PASSWORD_ENTERED to True if valid password
Case "Sheet4"
'Prompt for Valid Sheet 3 PassWord
'Flip PASSWORD_ENTERED to True if valid password
End Select
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
PASSWORD_ENTERED = False
End Sub
--
HTH...

Jim Thomlinson


Thanks for your reply. I copied your code and am modifying it as
needed. A couple of questions have occurred to me.

To be sure I'm understanding your overall approach, the sheets are not
literally password protected in the usual sense. But when someone tries
to change something, the code intervenes and won't allow any changes
without them entering the password. Is that correct?

Is there a simple way to ignore a series of sheets with sheetnames
ending in "monthly" in the excluded sheets list without entering them
all individually? I.e., sheets named "Joe S. - Monthly", etc.

When I prompt for their password, do I use an input box, userform? What
is the best way? I'm not so concerned about the password not showing
when they enter it, if it's a lot of trouble to make it appear as
asterisks. We're not that worried about security here.




"davegb" wrote:

I've been asked to write a macro to protect a worksheet from being
accidentally changed. There are about 10 users who each enter data on
their own specific worksheet, and each of them has the password for
their sheet only. (There are additional sheets and macros that tally
the information entered and graph it and so forth.)

What my customer wants is that if they try to make an entry, the
password box automatically appears (preferably in place of the box
warning that the worksheet is protected) so they can just type their
password in, make the changes they want, and when the close the
workbook or go to look at any other sheet, the worksheet they edited is
automatically password protected again. If they accidentally click in
someone else's worksheet, they will get the password box and just hit
Cancel and go to the correct page.

I read Walkenbach and did some research here, but I'm still not sure
how to approach this, even though I think once I figure it out, it'll
be fairly simple. Right now, I'm confused about the "Change" event.
Walkenback warns that it's not triggered by some kinds of actions that
you might expect it to be triggered by. I found there is both an
application level "Change" and a "SheetChange", but I'm not sure which
one would be best here. Any suggestions, both as to which is best and
any other ideas/warnings as to my approach?

This is not a security issue, just protection from an accidental entry
messing someting up.

Thanks in advance.