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.

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.