Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

How to automatically lock certain cells based on specific entry in some other cell?

0

Users can enter in cells of column A, B and C but I have locked column D in which only I can enter data. After checking the data entered in column A, B and C, I will approve it by typing "OK" in column D.

After my approval, I want the data in column A, B and C to be locked.

For example,

Users enter data in A1, B1 and C1 and I approve it by typing "OK" in D1. After my approval, I want A1, B1 and C1 to get locked automatically, so that the users can no longer edit or delete the data in these cells.

After entering "OK" in D1, only A1, B1 and C1 should be locked, and the users should be allowed to enter in A2, B2 and C2... A3, B3 and C3 and so forth till "OK" is entered in D2 and D3 respectively, and so forth.

How can I apply this conditional locking in excel?

Answer
Discuss

Discussion

Thanks. The locking of A1, B1 and C1 works fine, and even D1 gets locked, but after approving by entering "OK" in D1, how can I delete "OK" from D1 just in case I want to allow users to re-enter data in A1, B1 and C1?

And after entering "OK" in D1, I just want A1, B1 and C1 to get locked, and entry of "OK" in D2 will lock A2, B2 and C2 and so forth.

The current code locks the whole sheet, but after entering "OK" in D1, only A1, B1 and C1 should be locked, and the users should be allowed to enter in A2, B2 and C2... A3, B3 and C3 and so forth till "OK" is entered in D2 and D3 respectively, and so forth.
apurva0108 (rep: 2) Jul 24, '16 at 5:30 pm
I assumed that you were already using the protect sheet setup and had all of the other cells unlocked.

I updated my answer with how to keep the other cells unlocked. 
don (rep: 1989) Jul 24, '16 at 7:11 pm
Can you also please let me know how I can restrict other users from entering data in column D because this is the column which I use for approval by entering "OK"?
apurva0108 (rep: 2) Jul 28, '16 at 5:04 pm
Add to Discussion

Answers

0
Selected Answer

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 4 And UCase(Target.Value) = "OK" Then

    ActiveSheet.Protect Contents:=False
    Cells(Target.Row, 1).Locked = True
    Cells(Target.Row, 2).Locked = True
    Cells(Target.Row, 3).Locked = True
    ActiveSheet.Protect Contents:=True

ElseIf Target.Column = 4 And UCase(Target.Value) <> "OK" Then
    
    ActiveSheet.Protect Contents:=False
    Cells(Target.Row, 1).Locked = False
    Cells(Target.Row, 2).Locked = False
    Cells(Target.Row, 3).Locked = False
    ActiveSheet.Protect Contents:=True
    
End If

End Sub

Put it into the worksheet where you want it to run. In the VBA editor (Alt + F11) look to the left and double-click the name of the desired worksheet and paste the code in the window that opens.

Update

This assumes that all other cells are already unlocked.

The easiest thing to do is to select all of the cells in the worksheet > right-click > click Format Cells > go to the Protection tab > uncheck next to where it says Locked and click OK.

Then the above macro should work as expected.

Discuss

Discussion

It is working as expected.

After entering "OK" in D1, the cells A1, B1 and C1 get locked, but when I remove "OK" from D1, the cells A1, B1 and C1 are still locked, which I don't want.

After removal of "OK" from D1, I want the cells A1, B1 and C1 to get unlocked again so that the users are allowed to re-enter data in A1, B1 and C1.
apurva0108 (rep: 2) Jul 25, '16 at 10:01 am
Updated the answer. Try the new macro.
don (rep: 1989) Jul 25, '16 at 11:38 am
Great! The new macro is working perfectly. Thanks.

One more related query:
The current macro is for locking and unlocking column A, B and C. How can I add more columns by specifying a range instead of individually typing Cells(Target.Row, 1), Cells(Target.Row, 2), Cells(Target.Row, 3), etc. in the macro?
apurva0108 (rep: 2) Jul 27, '16 at 5:24 am
And is there a way to apply this macro in Google Sheets?
apurva0108 (rep: 2) Jul 27, '16 at 5:31 am
I know nothing about google sheets and you can select a range like this: Range("A1:C5") if you want to apply it to a specific range or something like this if it should be the same row: Range(Cells(Target.Row,1),Cells(Target.Row,5))
don (rep: 1989) Jul 27, '16 at 11:00 am
Thanks for sharing your expertise.
apurva0108 (rep: 2) Jul 28, '16 at 12:49 am
Can you also please let me know how I can restrict other users from entering data in column D because this is the column which I use for approval by entering "OK"?
apurva0108 (rep: 2) Jul 28, '16 at 5:03 pm
I was wondering when you were going to ask that ;). Start a new question for this though so we can keep questions separate. But make sure to explain what you need in the new question too so that users who only see that question can follow it.
don (rep: 1989) Jul 28, '16 at 6:02 pm
Ok. I will clear my query through a new question. Thanks.
apurva0108 (rep: 2) Jul 29, '16 at 4:14 am
I have started the new question:

"How to restrict users, apart from me, from entering data in a particular column?"
apurva0108 (rep: 2) Jul 29, '16 at 10:15 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login