Unprotect entire sheet
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 |
Unprotect entire sheet
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 |
All times are GMT +1. The time now is 04:16 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com