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 restrict users, apart from me, from entering data in a particular column?

0
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

I have already applied the above macro, which locks the cells A1, B1 and C1 after I enter "OK" in D1, and unlocks the cells A1, B1 and C1 when I remove "OK" from D1.

How can I restrict users, apart from me, from entering data in column D, which is used by me for approving by entering "OK" in it?

Answer
Discuss

Answers

0

Go to the Review tab and click Allow User to Edit Ranges and add data for yourself there.

Here is our tutorial on allowing specific users to edit specific ranges in Excel.

Discuss

Discussion

But the "Allow Users to Edit Ranges" option is greyed out and I am unable to select it.

Does it have something to do with the above macro which I have already applied after unlocking all the cells in the sheet?
apurva0108 (rep: 2) Jul 29, '16 at 10:36 am
You need to make sure the sheet isn't currently protected. Review tab > Unprotect Sheet
don (rep: 1989) Jul 29, '16 at 11:02 am
It is not working.

After creating a password to protect column D, when I delete "OK" in column D, a dialog box asks for a password, which is fine, but in the dialog box if I select "Cancel" without entering a password, it shows the following error:

<Run-time error '1004':

Unable to set the Locked property of the Range class>

Then I clicked "Debug" which highlighted the following line in the macro:

Cells(Target.Row, 1).Locked = False
apurva0108 (rep: 2) Jul 29, '16 at 4:25 pm
Using the macro you have in  your question and following the instructions in the tutorial to which I linked, I do not get any errors at all. Do you have some other vba code that you are using?
don (rep: 1989) Jul 30, '16 at 3:56 pm
I am not using any other vba code other than the one I have posted above.

Can you please let me know the sequence of steps through which I can achieve the required task?
apurva0108 (rep: 2) Jul 31, '16 at 5:44 am
Install the macro above and follow the steps in the tutorial to which I linked.

If you can upload your workbook to your question, it will be much easier to troubleshoot any problems.
don (rep: 1989) Jul 31, '16 at 7:57 am
I have uploaded the excel file in which the above macro has been applied.

Please let me know how to get the job done.
apurva0108 (rep: 2) Aug 4, '16 at 3:56 pm
Were you able to check the attached excel file?
apurva0108 (rep: 2) Aug 7, '16 at 12:13 am
Add to Discussion


Answer the Question

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