Add user edit range

[gvreddyhr]

Hi,

Refer to above, I have excel which allows users to enter the data in range of A1:d(.xldown), when user update the data in a1:d1 then it should allow user to only edit a2:d2 and protect all ranges including a1:d1 from editing, request anyone of you to help me with the code.

Below is the code, can anyone help me in tweaking the same.

[vba]
Sub AddUserEditRange()
Dim ws As Worksheet, aer As AllowEditRange
Set ws = ThisWorkbook.Sheets("Protection")
ws.Unprotect "Excel2003"
Set aer = ws.Protection.AllowEditRanges.Add("User Range", ws.Range("A11"))
aer.Users.Add "Power Users", True
ws.Protect "Excel2003"
End Sub

[/vba]

Thanks in advance

Regards,
GR

[Bob Phillips]

Set the locked property of the cells A of the next row to unlocked, and add this code to the worksheet module

[VBA]Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit

Application.EnableEvents = False

Me.Unprotect

With Target

If Application.CountA(Me.Cells(.Row, "A").Resize(, 4)) = 4 Then

.Parent.Cells.Locked = True
Me.Cells(.Row + 1, "A").Resize(, 4).Cells.Locked = False
End If
End With

ws_exit:
Me.Protect
Application.EnableEvents = True
End Sub
[/VBA]

[gvreddyhr]

Thanks for your Qucik response, I have updated the code which provided, but that doesnt seem working, am enclosing the workbook for your easy reference.

If we enter the data till A2 to D2, (D2 = Completed) then it should get locked and A3 to D3 should be unlocked, if (d3=completed) then A4 to d4 should get unlock..

CAn you help on this please

Regards,
GR

[Bob Phillips]

As I said, you have to unlock the four cells in columns A:D of the current row to get you started, the code does it from thereon.