How to Lock Row or Range of Cells After Data Entry of Specific Range of Cells - Excel 2010

[nryan]

Hello Forum,

I've searched quite a bit on this topic but haven't found anything helpful to my needs so far. I'll organize this thread in headers to help you find things more easily.

What I Want to Do is This:
Once data is entered in both K and L columns, I want to lock the row which the data was just entered into. It's important that the order which the data is entered doesn't matter (K first or L first). If the word "lock" is a little vague basically I mean I don't want the user to be able to modify or delete the data (or the entire row!) after data has been entered. That is, unless they have the password . I don't mind if the row is locked using a macro or data validation or conditional formatting, although I'm more familiar with macros than with the other two so I may have trouble understanding how to implement the data validation or conditional formatting unless you speak to me like a newbie (which I am). I also don't mind locking a range of cells (for example A2, B2, C2, D2, E2, ... , K2, L2, and M2) after the data entry (into both K2 and L2) if that is an easier thing to do than locking the entire row.

Nice Bonus Feature:
It'd be nice if the row fill color changed from white to light grey or light blue when it goes from unlocked to locked. I can probably figure out how to add that feature if the locking is done using a macro.

Worksheet Info:
The data entry is ongoing so the last row is unknown. I have a macro that automatically protects all worksheets upon opening the workbook (with a password) and users are allowed to select only unlocked cells for which to enter data.

Extra Info:
The password I use to protect all worksheets is given in the VBA code and that is not an issue. I'm not worried about users getting into the VBA code and seeing the password and using it to unlock the row(s). If that were to happen that would be a whole different problem.

Thank you
-Nick

 

[nryan]

I found a good option for locking a cell after data entry. This works for me. I visualized a different solution but this one does what I need.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim XYData As Range


Set XYData = Intersect(Range("K2:L100000"), Target)


If Not XYData Is Nothing Then
    Worksheets("ACP").Unprotect Password:="[your password goes here]"
    XYData.Locked = True
    XYData.Interior.Color = RGB(200, 200, 200)
    Worksheets("ACP").Protect Password:="[your password goes here]"
End If

End Sub

Hope this is helpful to someone.
-Nick

 

[harveya915]

where would this code go?
Can it be applied to a UserForm to where when i hit the "OK" button, this rule takes effect?

 

[harveya915]

I figured out to put it in to the sheet code and not the userform code. but now, when i share the sheet, the code doesn't work. it only works when i don't have it in shared. how can i fix this