Deceptively difficult? Lock a range of cells (block) after enter a specific value in one cell

[lucian688]

Hi I have spreadsheet & I am hoping to have a block of cells (e.g., A1:D10) locked upon changing a cell (e.g., F15 having an entry of "Yes"). I.e., any other value such as "No" will not lock cells A1:D10. Additionally, anyway to embed the code without having to run the macro each time opening up the spreadsheet would be nice.

Any tips are greatly appreciated! Thank you!!
Luke

 

[JoeMo]

Okay let me give it another shot to clarify. So the idea is to add a name stamp "John" anywhere/any cell on the worksheet before the entire sheet is locked to prevent tempering.

So in the example above, by change F15 to "Yes", let's say F16 will print "John" then the entire worksheet is locked.

This should do what you have requested assuming the sheet is protected, but with whatever cells you want the user to enter "Yes" in unlocked to begin with. If the user enters "Yes" in any one of those cells, the cell immediately below will be stamped with "John" and ALL cells will be locked. This means you will have to unprotect the sheet to make any further changes to it.

Private Sub Worksheet_Change(ByVal Target As Range)
Me.Protect Password:="pswd", userinterfaceonly:=True
Me.Cells.Locked = False
Set Target = Target.Cells(1, 1)
If Target.Value <> "Yes" Then Exit Sub
Target.Offset(1, 0).Value = "John"
Me.Cells.Locked = True
End Sub

Substitute your password for the one in bold red.

 

[lucian688]

Wait, the worksheet will need to be unprotected to allow entry. Then, let's say the reviewer John will evidence his review in Cell F15 "Yes", and a name stamp should appear in Cell F16 "John", then the worksheet is locked. Is this possible?

 

[JoeMo]

Wait, the worksheet will need to be unprotected to allow entry. Then, let's say the reviewer John will evidence his review in Cell F15 "Yes", and a name stamp should appear in Cell F16 "John", then the worksheet is locked. Is this possible?

That's what the code is doing now. But, after John enters his "Yes", because you want the name stamp in the same column as the Yes, in order to protect the stamp, that column, along with the rest of the sheet, per your request in post #10 has to ne locked. At that point, no user can make any entry to the sheet until the sheet is unprotected by someone who has the password.

Why not stamp the name in another column and leave the Yes column unlocked? The code below will stamp the name in the same row as the Yes, but in the next column to the right. So, if the user enters "Yes" in F15, the name will be put in G15. Any user can subsequently delete the Yes, but not the name.

Private Sub Worksheet_Change(ByVal Target As Range)
Me.Protect Password:="pswd", userinterfaceonly:=True
Set Target = Target.Cells(1, 1)
If Target.Value <> "Yes" Then Exit Sub
Target.Offset(0, 1).Value = "John"
Me.Cells.Locked = True
Range("F:F").Cells.Locked = False
End Sub