Excel crashes - nested IFs?

[DDelainy]

Hello there,
When I run this code excel crashes.

Task: Cell F42 has drop down options YES/NO
If F42=Yes, unprotect sheet, unlock cell K42 (merged with L42), protect sheet
If F42=No or empty, unprotect sheet, unlock cell K42:L42, clear contents of K42:L42, lock cell K42:L42, protect sheet

Main reasoning behind second step is if selection from F42 cleared, I need K42:L42 to be cleared as well and protected.

I have this code, if works fine for "Yes" portion, but once I select "No" or clear the contents of F42, Excel crashes :

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("F42") = "Yes" Then
        ActiveSheet.Unprotect "Password1"
        Range("K42:L42").Locked = False
        ActiveSheet.Protect "Password1"
    Else
        If Range("F42") <> "Yes" Then
        ActiveSheet.Unprotect "Password1"
        Range("K42:L42").Select
        Selection.ClearContents
        Else
        Range("K42:L42").Locked = True
        ActiveSheet.Protect "Password1"
        End If
    End If
End Sub

Thanks!

 

[StephenCrump]

Selection.ClearContents is a Worksheet_Change, which will trigger the Sub, etc, etc, i.e. the code gets stuck in a loop.

If you're going to make a worksheet change inside a Sub Worksheet_Change, you need to wrap in

Application.EnableEvents = False
'make change(s) here
Application.EnableEvents = True

Your code logic is also not quite right. You don't need to test: Range("F42") <> "Yes", and your code can never get to this line: Range("K42:L42").Locked = True

I think this is what you're after? (And I assume F42 is unlocked?)

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim r As Range
    
    If Intersect(Range("F42"), Target) Is Nothing Then Exit Sub
    
    ActiveSheet.Unprotect "Password1"
    
    With Range("K42:L42")
        .Locked = Range("F42") <> "Yes"
        If .Locked Then
            Application.EnableEvents = False
            .ClearContents
            Application.EnableEvents = True
        End If
    End With
    ActiveSheet.Protect "Password1"

End Sub

 

[DDelainy]

That works perfectly, thanks for the insight @StephenCrump

 

[StephenCrump]

Great! I'm glad it's working for you.

 

[DDelainy]

Hi @StephenCrump, I thought you'd be able to help again. The document I created is now bilingual (EN/FR). There is a toggle button between English and French, and value of same cell F42 (which is either English or French dropdown), can be either "Yes" or "Oui" for the Range("K42:L42") to be affected.

Could we integrate this in the code? I tried figuring out and adding Else function, but I didn't succeed. Thanks!