View Full Version : Add user edit range

02-11-2013, 01:52 AM

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.

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("A1:D1"))
aer.Users.Add "Power Users", True
ws.Protect "Excel2003"
End Sub

Thanks in advance


Bob Phillips
02-11-2013, 05:08 AM
Set the locked property of the cells A:D of the next row to unlocked, and add this code to the worksheet module

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit

Application.EnableEvents = False


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

Application.EnableEvents = True
End Sub

02-11-2013, 11:46 PM
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


Bob Phillips
02-12-2013, 01:29 AM
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.