how to auto lock a cells value after data entry

colleen

Board Regular
Joined
Feb 21, 2002
Messages
61
I wish to know is it possible to lock a cell automatically after data is entered and saved ..ie I enter data in rows is certain columns so that after I save it, so that who ever opens it the next time should not be able to change that cell value but only enter new data in a subsequent row cell..and also is there a way where only I can change that cell value by using say a password..
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Maybe this can help you.

I created a new workbook, selected all the cells, and UNCHECKED the locked option for all of them.

Next, I protected the sheet, using as password

Password

then, I put this code in the Sheet's module.

<pre>Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect "Password"
Target.Locked = True
ActiveSheet.Protect "Password"
End Sub</pre>
Then, everytime I change a cell, it becomes locked, that means that you can't write on it again.

You could modify them later using Tools | Protect | Unprotect sheet, and supplying the password (Password in this case)
 
Upvote 0
Hi, the code works well, but I need to lock the entire row or a defined range in that row, like A1:G1. Also, as a correction if the user wants to leave a cell blank, it should stay unlocked as if there was never anything entered in it before. Scenario. User enter the date in A1 and later wants to delete it. He will first unprotect the sheet and clear the contents of he cell A1. At this point, base on the condition that the cell is empty it should stay editable.
 
Upvote 0

Forum statistics

Threads
1,218,652
Messages
6,143,720
Members
450,502
Latest member
sakaria_123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top