I have a sheet that is protected so users can enter data is certain cells
without a password. When they do, when and who is tracked on another sheet.
The problem is, I want to be able to enter data in all cells. If I unprotect
the sheet, the code forces me to unprotect the sheet for each cell. Here is
the code I am using:

Private Sub Worksheet_Change(ByVal Target As Range)
Const PW As String = "my password"
ActiveSheet.Unprotect Password:=PW
If firstime = True Then
firstime = False
n = Sheets("changes").Cells(Rows.Count, 1).End(xlUp).Row + 1
Application.EnableEvents = False
Sheets("changes").Cells(n, 1).Value = Now
Sheets("changes").Cells(n, 2).Value = Environ("UserName")
Application.EnableEvents = True
End If
ActiveSheet.Protect Password:=PW
End Sub

I'm confused. Your code sample doesn't seem to match the problem you are
describing. You are only modifying two cells in your sample code. Are you
saying the sample code you provided doesn't work?

Your description says you are protecting one sheet and tracking information
on another sheet. Which sheet is triggering the Change event? I would
assume the sheet the user changed is triggering the Change event. But,
then, I don't understand the reason for unprotecting it; they've already
made the changes. Is the "changes" sheet also protected?

BTW, why use ActiveSheet when you already have the worksheet pointer; i.e.,
Target.Worksheet. But, again, your description says you are protecting one
sheet and tracking information on another sheet. Which sheet is triggering
the Change event? I would assume the sheet the user changed is triggering
the Change event. But, then, I don't understand the reason for unprotecting
it; they've already made the changes. The problem with using ActiveSheet is
that it could change while your code is running if you ever yield (i.e.
DoEvents).


"Homer" wrote in message
...
I have a sheet that is protected so users can enter data is certain cells
without a password. When they do, when and who is tracked on another
sheet.
The problem is, I want to be able to enter data in all cells. If I
unprotect
the sheet, the code forces me to unprotect the sheet for each cell. Here
is
the code I am using:

Private Sub Worksheet_Change(ByVal Target As Range)
Const PW As String = "my password"
ActiveSheet.Unprotect Password:=PW
If firstime = True Then
firstime = False
n = Sheets("changes").Cells(Rows.Count, 1).End(xlUp).Row + 1
Application.EnableEvents = False
Sheets("changes").Cells(n, 1).Value = Now
Sheets("changes").Cells(n, 2).Value = Environ("UserName")
Application.EnableEvents = True
End If
ActiveSheet.Protect Password:=PW
End Sub