Locking cells after entry using VBA

[Cossie]

G'day everyone ~ Thanks again for your assistance

I am trying to write some code that will allow me to lock cells after data has been entered.

What i have in my example code below locks cell b1. What i would like is to lock a range of cells after a user has entered some information.

eg user enters data in cells A1:F1 and then these cells lock. The next user enters data into A2:F2 and thes cells then lock and the next users enters data into A3:F3 and these cells then lock etc etc etc.

This is what I have at the moment:


Private Sub Worksheet_Change(ByVal Target As Range)
Dim Answ, Rng As Range

Application.ScreenUpdating = False
Set Rng = Application.Intersect(Target, Range("b1"))
If Target.Cells.Count > 1 Then Exit Sub
If Not Rng Is Nothing Then
Answ = MsgBox("This cell will now be locked.", vbOKCancel, "Confirm Change")
If Answ <> vbOK Then
Application.EnableEvents = False
Target.ClearContents 'clear contents if cancel is pressed
Application.EnableEvents = True
Exit Sub
End If
ActiveSheet.Unprotect "1"
Target.Locked = True
ActiveSheet.Protect Password:="1", DrawingObjects:=True, Contents:=True, Scenarios:=True
End If
Application.ScreenUpdating = True
End Sub

 

[RoryA]

It needs to be in the worksheet code module for the relevant sheet. It will run automatically when you make changes to the sheet.

 

[kelly mort]

It needs to be in the worksheet code module for the relevant sheet. It will run automatically when you make changes to the sheet.

This code seems to lock all cells. What if you wanna lock only the one that you have entered data in?
Kelly

 

[RoryA]

You need to make sure that all the cells you want editable at the start have the Locked property set to False (it's True by default).

 

[kelly mort]

Okay done. So what it i dont want to be able to select a locked cell?

 

[RoryA]

Set the Worksheet's EnableSelection property to xlUnlockedCells.

 

[kelly mort]

Set the Worksheet's EnableSelection property to xlUnlockedCells.

Which part in the code?
I am new to this

 

[RoryA]

ActiveSheet.Unprotect "bob"
Target.LockedRow = True
Activesheet.EnableSelection = xlUnlockedCells
ActiveSheet.Protect Password:="bob", DrawingObjects:=True, Contents:=True, Scenarios:=True

 

[kelly mort]

Okay thanks