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.

The change event is not triggered by pasting into a cell, which is a real
pain.

Workbook SheetChange is the same as worksheet change, just that it applies
to all sheets, not just the sheet the code is hosted by.

Another thought, why don't you test in the workbook open for the user
(Environ("UserName")), and then hide all worksheets (you can very hide in
VBA) except the worksheet pertaining to them.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"davegb" wrote in message
ups.com...
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.

Thanks for your reply. I don't follow some of your logic, maybe my
description of the requirements was unclear. See my questions below.

Charles Chickering wrote:
Dave, I would use a combination of the SheetChange and SelectionChange
events. The change event fires only after you get into the cell, which does
not bypass the protected cell message. Use a global variable to store whether
or not the user has entered a password. I would use logic similar to the
following for this:
On workbook open all sheets are unlocked

Why unlock them all when the user only needs access to his/her own?

On selection change the user is prompted for a password
On incorrect password, protect the sheet

Why protect the sheet when, if the correct password hasn't been
entered, it will already be protected? Maybe you are suggesting that
the code unprotect all sheets, then re-protect the one the user is
trying to access if they enter the wrong password? Why is that approach
simpler than just working with the current user's worksheet?

On correct password, set the global variable for the user
having logged in to true, make sure that the selection change event
exits sub if this variable is true
On SheetChange reset the variable to false and unprotect the previous sheet

Again, not sure why other sheets need to be protected/unprotected. I
think my explanation must not have been clear on this.

The unprotect of the sheet might be able to be done in the sheet
deactivate event

Not sure why I'd want to unprotect when the worksheet or workbook is
deactivated. Seems to me I want to protect at that point. Am I missing
something?


Post back if you need more help

Thanks again!

--
Charles Chickering

"A good example is twice the value of good advice."


"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.

Bob Phillips wrote:
The change event is not triggered by pasting into a cell, which is a real
pain.

Workbook SheetChange is the same as worksheet change, just that it applies
to all sheets, not just the sheet the code is hosted by.

Another thought, why don't you test in the workbook open for the user
(Environ("UserName")), and then hide all worksheets (you can very hide in
VBA) except the worksheet pertaining to them.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


Thanks for your replay, Bob. This seems like an interesting approach.
I'm not familiar with the "Environ" property/method, but I did some
research on it. Not sure how I'd write the code to use it yet. Will try
another approach for now, but I will come back to this when I have some
time to see how it would be done.

"davegb" wrote in message
ups.com...
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.

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.

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.