Data enter in cell to be convert as read only

[vvv999vvv]

Dear,

I like to have solution,

Data once entered in cell: convert to read only
Still any other can enter data: in remaining blank cell
After enter data in new cell: become read only again
If like to change/edit in cell (having data): can be only by password

Data sheet opening encrypted by: another password
Not by Digital Signature: because sign anybody can remove

Thanks in advance & Regards

vvv999vvv

 

[jbeaucaire]

This is kind of basic, but it works. First, uncheck the protection box in all the cells that you want to be editable. Do this FIRST.

Then right-click on the sheettab and VIEW CODE, then paste in this code to the VBEditor Sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect Password:="pwd"
    Target.Locked = True
ActiveSheet.Protect Password:="pwd"
End Sub

Now any cell that changes value will become locked, immediately.

 

[vvv999vvv]

Pl. answer with detail


Thanks

 

[jbeaucaire]

Pl. answer with detail -Thanks

Every step was in the post above.

1. Uncheck the protection box in all the cells that you want to be editable.
2. Right-click on the sheettab name
3. Select VIEW CODE
4. A window will appear
5. Copy the following code from this forum into the window
   

   Private Sub Worksheet_Change(ByVal Target As Range)
   ActiveSheet.Unprotect Password:="[COLOR="Red"]pwd[/COLOR]"
       Target.Locked = True
   ActiveSheet.Protect Password:="[COLOR="Red"]pwd[/COLOR]"
   End Sub

6. Edit the password to what you want it to be, it's "pwd" in the code right now
7. Alt-F11 to close the Editor
8. Save your sheet
9. Now make a change on your sheet in a cell you had unprotected
10. Return to that same cell and try to change it again, it should stop you indicating the cell is locked.

 

[jbeaucaire]

Another version of the code, same idea, one less line:

Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet.Protect Password:="pwd", UserInterfaceOnly:=True
    Target.Locked = True
End Sub

If you don't know how to set the protection in cells:

1. Highlight the cells you want them to be able to edit
2. Press Ctrl-1 to open the Format Cells window
3. Click on Protection
4. Uncheck the "locked" box
5. Press OK

When the sheet is protected, all cells that have that checked box will be uneditable, which is why you want to uncheck it prior to protecting the sheet. Now, anytime one of the cells you DID unlock get edited in any way, it will be locked.

 

[vvv999vvv]

Thanks 4 ur efforts,
Still all remaining cells (with data) are not converting to read only.

May I miss someting.

any othe setting may usefull?

Regards