I have a simple workbook with about 12 sheets - no other code, no macros, no
ranges. My code is at end of post. What I am trying to do is to automatically
unprotect the sheets when certain users open it (based on username) and have
all sheets protected when everone else opens it. Only 2 users on my network
are to have ability to edit any field on all worksheets. It works, but there
are a couple of anomalies.

I'm using unprotect code on Workbook_open and similar code to protect on
Workbook_BeforeClose. To try and solve why sometimes a sheet becomes
unprotected, I have also added this to Workbook_SheetActivate.

1) As I test after workbook opens with protection, I will choose the
unprotect from Tools menu on a sheet or 2 and then I move to another sheet
and all of a sudden, when I choose the file option, I do not get the password
dialog. This is random, as when I close book, open again and go to sheet that
was unprotected in my previous test, it is protected. Then I go to a couple
more sheets and it happens again. Sheets seem to become unprotected somehow
without using code or entering password.

2) Some fields in some of my worksheets don't have protection. Most do, but
as I randomly check fields, all of a sudden I come across one that just is
not protected. Again, there are no special features, other than auto
filtering.

I am running 2003 on XP.

Thanks,
Clint

MY CODE:

Dim wsSheet As Worksheet
Const PWORD As String = "testing"

If Environ("UserName") = "cherman" Then
For Each wsSheet In Worksheets
wsSheet.Protect Password:=PWORD
Next wsSheet
Else
For Each wsSheet In Worksheets
wsSheet.Unprotect
Next wsSheet
End If

Hello Cherman,

You maybe experiencing a timing problem. Code execution and othe
operations are not always synchronous. The For Each may be the proble
here. This method of looping is much faster than the old trusty Fo
Next loop. You maybe loop faster than VBA can unprotect the shee
causing the anomolies. Try inserting a delay in your loop or recode i
with a For Next loop

Sincerely,
Leith Ros

--
Leith Ros
-----------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846
View this thread: http://www.excelforum.com/showthread.php?threadid=47838