Auto-lock a cell once it's updated

  • Thread starter Thread starter Oscar
  • Start date Start date
O

Oscar

Hi.

I have a shared workbook that is used by two processors. On column A I have
a drop down that allows the processors to select Yes or No. I would like the
cell that they update to automatically lock by not allowing the processors to
go back and change what they already selected. If they try to change the
info, I would like it to prompt a password request in order to make a change
on that cell. If this is possible please let me know.

Thanks
 
Sheet event code..........to be pasted into the sheet module.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
With Me
.Unprotect Password:="justme" 'password to suit
n = Target.Row
If .Range("A" & n).Value <> "" Then
.Range("A" & n).Locked = True 'adjust the "A" if you wanted
'column B locked
End If
End With
End If
enditall:
Application.EnableEvents = True
Me.Protect Password:="justme"
End Sub

When happy with code, hit Tools>VBAProject Properties and protect the code
from view.

Alt + q to return to Excel.


Gord Dibben MS Excel MVP
 
Thanks for the quick response. I used the code and it works; however, I
noticed that when I try to update the next row on column A or any other field
outside of column A, it ask me to unprotect the worksheet using the password.
Would it be possible to only ask to unprotect the sheet if I tried to go back
and delete or update a row I already updated under column A?

Thanks
 
I guess I assumed you would Unlock ALL cells on the worksheet prior to
adding the code.

Apologies for not pointing that out.

If you did not then go back and start again.

Unprotect the sheet.

Select all cells and Format>Cells>Protection......uncheck Locked.

Now add the code.

As users select from yes/no dropdown that cell will become locked.

To edit you must enter password to unprotect.


Gord
 
Back
Top