Lock and unlock conditional on specific cells

[robinho0d]

Hello, everyone
I am trying to make a macro that, if cells A36, A37 and A38 contain the numbers 1 to 3, lock the adjacent cell B36 or B37 or B38 or all cells depending on whether they contain the numbers 1 to 3.
I've tried quite a bit of searching on the internet and trying to help myself, but I can't get the macro I'm uploading.
I am asking for some help from you.
Thanks in advance to each one!


'
Private Sub Worksheet_Change(ByVal Target As Range)

Sub tedsst()
Application.ScreenUpdating = False
ThisWorkbook.Worksheets("bANANAS").Unprotect ("k0st4d")
'ActiveSheet.Unprotect Password:="123" '->>> How to say witch Sheet name i.e "Banana"
If Range("A36") = ">=3" Then
Range("B36").Locked = False
ElseIf Range("A36") = "<3" Then
Range("B36").Locked = True
End If
If Range("A37") = ">=3" Then
Range("B37").Locked = False
ElseIf Range("A37") = "<3" Then
Range("B37").Locked = True
End If
If Range("A38") = ">=3" Then
Range("B38").Locked = False
ElseIf Range("A38") = "<3" Then
Range("B38").Locked = True
End If
ActiveSheet.Protect Password:="123"
ThisWorkbook.Worksheets("bANANAS").Protect ("k0st4d")
Application.ScreenUpdating = True
End Sub

[jdelano]

If you're testing for a numeric value, then don't put it in quotes. So, this
If Range("A36") = ">=3" Then would be
If Range("A36") >= 3 Then and so forth.

You should also target a specific worksheet with your Range.


Sub tedsst()

Dim wkSheet as Worksheet
Set wkSheet = ThisWorkbook.Worksheets("bANANAS")

Application.ScreenUpdating = False
wkSheet.Unprotect ("k0st4d")
'ActiveSheet.Unprotect Password:="123" '->>> How to say witch Sheet name i.e "Banana"

If wkSheet.Range("A36") >=3 Then

wkSheet.Range("B36").Locked = False
ElseIf wkSheet.Range("A36") <3 Then


wkSheet.Range("B36").Locked = True
End If

If wkSheet.Range("A37") >=3 Then


wkSheet.Range("B37").Locked = False
ElseIf wkSheet.Range("A37") <3 Then


wkSheet.Range("B37").Locked = True
End If


If wkSheet.Range("A38") >=3 Then


wkSheet.Range("B38").Locked = False
ElseIf wkSheet.Range("A38") <3 Then


wkSheet.Range("B38").Locked = True
End If

wkSheet.Protect ("k0st4d")
Application.ScreenUpdating = True

End Sub


You can shorten this code by assigning the locked value the value of testing the <3 in the cells. Like


Sub tedsst()

Dim wkSheet as Worksheet
Set wkSheet = ThisWorkbook.Worksheets("bANANAS")

Application.ScreenUpdating = False
wkSheet.Unprotect ("k0st4d")
'ActiveSheet.Unprotect Password:="123" '->>> How to say witch Sheet name i.e "Banana"

wkSheet.Range("B36").Locked = (wkSheet.Range("A36") < 3)
wkSheet.Range("B37").Locked = (wkSheet.Range("A37") < 3)
wkSheet.Range("B38").Locked = (wkSheet.Range("A38") < 3)

wkSheet.Protect ("k0st4d")
Set wkSheet = Nothing
Application.ScreenUpdating = True

End Sub


Because the locked property should be True if wkSheet.Range("A36") < 3 is true, you can just assign the value of the comparison directly and you don't need to have the if statements.

[robinho0d]

Hello,
you are absolutely right.
The idea is that I was trying to do it myself and these quotes or the so-called split cell definition is because, I don't know how to do it. Like you did it and it looks a thousand times better.
Now I will try it in my table and write again.
Thanks in advance.

'Private Sub Worksheet_Change(ByVal Target As Range)
I'm thinking that in the Bananas worksheet itself, shouldn't this be so that it can execute it when the book is opened, rather than activating it with a button for example?

[jdelano]

Yes, you can put the code in the Worksheet_Activate event, so whenever it is viewed it runs the code, or in the deactivate event so whenever someone leaves the sheet it locks what needs to be locked for the next time.

[robinho0d]

I'm messing up with the placement of the macro somewhere.
Am I doing it right??
I want when the workbook is opened, and in those cells during the work, If the numbers 1 to 3 appear there, the macro will lock them at once.
Example: I open and in the mentioned cells are the numbers 29, 30, 31, change something (because there in those cells I have formulas that take information). Taking the info, just then, these numbers 1,2,3 can appear. It follows that the macro should lock them.
Now, let me ask: Should I put this macro in the Bananas worksheet or in ThisWorkbook ?

[jdelano]

You can put it in ThisWorkbook in the open event Workbook_Open and you can put it in the Change event of the worksheet if you want it to lock the cells as things are edited. If not, you can put in in the Sheets activate event, so the cells are locked when the sheet is displayed.

Put the code in a few places to see where it works best for you.

[robinho0d]

I am an oligophrenic
I try it in the sheet itself, changing the title - Change , Open , standalone SUB .
I'm making a mistake somewhere.
I put it in ThisWorkbook as
Private Sub Worksheet_Open(ByVal Target As Range)

Private Sub Worksheet_Change(ByVal Target As Range)
going to the cells allows me to write, no problem.