How would I go about lock a cell after there is an entry?

Question

Hi

How would I go about lock a cell after there is an entry. Once the entry has been made the user could not change the entry. For example if a user enter data in a cell, save then he could not change that entry but able to change any other cell rather than that. in other words, after a cell being modified, be lock and prevent further modification ONLY in that cell unless a password is provided. (the worksheet is protected)

Thanks. ( and if u don't mind, please reply simple, I'm not too familiar with VB or macro terminology :( )

Thanks.

I hope it is clear

Thanks

Denis

Answer 1

Well, no way to do this without a macro or add-on.

Even then, not exactly fool-proof I'd say. But, just for fun, let's try something here...

First, you need to get into the Visual Basic Editor. Do that by hitting Alt+F11 with your F-Lock on. Or,

Go to "Tools" >> "Macro" >> "Visual Basic Editor"

Now, you should see the Project Explorer window on the left side of the screen..if you don't, Hit CTRL+R or go to view and click on "Project Explorer".

Now, you should see one VBA Project in there for each Excel Workbook you have open. Find the one that you want to put this in, and click on the plus to expand it. Then, you should see a folder that says "Microsoft Excel Objects". Again, click on the plus sign to expand.

For a new workbook, you will see 4 objects: 3 of them represent worksheets, one represents the entire workbook. We're going to have to work with the worksheet you want protected. Go ahead and double click on the worksheet you want to be protected. if you want all of them, just repeat the next steps for each one.

Now it's easy. All you have to do is copy and paste the following code into the window.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim password As String

'==========================

password = "password"

'==========================

ActiveSheet.Protect Contents:=False, password:=password

Target.Locked = True

ActiveSheet.Protect Contents:=True, password:=password

End Sub

That's the end of the code. Don't copy the rest of this...

Your password will be specified between the double lines that will appear (probably) green when you copy this in. The word "password", in the quotes, is the one you want to change. So, if you want your password to be "aardvark", that line should look like this:

password="aardvark"

Oh one more thing though...

Back in the project explorer, right click on the project (the line above the Microsoft Excel Objects folder). Click on VBA Project Properties. Then, go to the Protection tab, and check 'Lock for Viewing'. Enter a unique password. That way, your user can't simply go in and look what your password is defined as.

Best of luck.

EDIT EDIT EDIT!!!

Jeez if you've done this already, you're probably having a headache figuring it out..

Before you implement this, you need to select all of the cells on the worksheet that you want data to be able to be entered into...

Then click "Format" > "Cells"

Under the "Protection" tab, uncheck "Locked". Otherwise, after you enter into the first cell, all cells will be locked out!