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]

Locking cells does not block entries to them unless the sheet is protected. Is your sheet protected? If not, would protecting it be acceptable?

Entries can be made to unlocked cells on a protected sheet. A bit of event code could be used to automatically lock a designated block of cells whenever "Yes" is entered in a specific cell.

 

[lucian688]

I tried the following... 1st of all it didn't work & 2nd I think it only locks the entire worksheet not the block. Help needed!
_______________________

Worksheet_Change(ByVal Target As Range)

If Target.Address = Range ("F15").Address Then
If Target.Value = "Yes" Then
ActiveSheet.Unprotect Password = "password"
Else
ActiveSheet.Protect Password = "password", DrawingObjects:=True, Contents:=True, Scenarios:=True
End If
End If

End Sub

 

[lucian688]

So it seems that I should lock/protect the sheet 1st. Basically there are 2 users involved. User 2 is an approver & upon approving data entry (block of cells) by User 1, the block of cells should be locked - i.e., User 2 approve the entry & give it back to User 1.... User 1 cannot change the value anymore.

I tried the code above as a skeleton start & it went nowhere everything locked. haha

 

[JoeMo]

See if this works for you. Change the password (in red font) to suit.

Private Sub Worksheet_Change(ByVal Target As Range)
Me.Cells.Locked = False
Me.Protect Password:="pswd", userinterfaceonly:=True
Set Target = Target.Cells(1, 1)
If Target.Address <> "$F$15" Then Exit Sub
Select Case UCase(Target.Value)
    Case "YES": Range("A1:D10").Locked = True
    Case Else: Range("A1:D10").Locked = False
End Select
End Sub

 

[lucian688]

Absolutely Brilliant! One last ques: if I want to have the data locked in another/adjacent worksheet "Sheet2" upon entry of "Yes" in "Sheet1" how would I adjust the value?

 

[JoeMo]

Absolutely Brilliant! One last ques: if I want to have the data locked in another/adjacent worksheet "Sheet2" upon entry of "Yes" in "Sheet1" how would I adjust the value?

Qualify the sheet like this:

Case "YES": Sheets("Sheet2").Range("A1:D10").Locked = True

BTW in the single sheet case, you probably should lock F15 too so another user can't delete the "Yes" that the supervisor entered. If you want to do that try replacing the code I posted earlier with this:

Private Sub Worksheet_Change(ByVal Target As Range)
Me.Cells.Locked = False
Me.Protect Password:="pswd", userinterfaceonly:=True
Set Target = Target.Cells(1, 1)
If Target.Address <> "$F$15" Then Exit Sub
Select Case UCase(Target.Value)
    Case "YES": Union(Range("A1:D10"), Range("F15")).Locked = True
    Case Else: Union(Range("A1:D10"), Range("F15")).Locked = False
End Select
End Sub

 

[lucian688]

I have another tag on question... thanks to Joe for the previous work btw.

So upon changing the fields to "YES", in addition to locking the block of cells. How can I enter a value in another cell such as "JOE". Then lock that cell

OR a simpler approach...

Upon changing the fields to "YES", add a value "JOE" to one of the cells on the worksheet then LOCK the entire sheet (password protected). So here is the idea - once the reviewer flick the field to "YES" ultimate goal is to lock the entire worksheet to prevent further tempering... but locking occurs immediately after a Name stamp is added to the worksheet for identification.

Also I assume the worksheet has to be "unlocked" prior to running the script...?

Thanks again!

 

[JoeMo]

I have another tag on question... thanks to Joe for the previous work btw.

So upon changing the fields to "YES", in addition to locking the block of cells. How can I enter a value in another cell such as "JOE". Then lock that cell

OR a simpler approach...

Upon changing the fields to "YES", add a value "JOE" to one of the cells on the worksheet then LOCK the entire sheet (password protected). So here is the idea - once the reviewer flick the field to "YES" ultimate goal is to lock the entire worksheet to prevent further tempering... but locking occurs immediately after a Name stamp is added to the worksheet for identification.

Also I assume the worksheet has to be "unlocked" prior to running the script...?

Thanks again!

The script is triggered by changes to the worksheet, runs automatically, and never needs to be unprotected ("unlocked") to run. Your tag on question is vague at best. For example, where exactly is "another cell" on the worksheet.

 

[lucian688]

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.