Excel 2013 stop working when using VBA to lock/unlock cell

Question

I am facing Excel 2013 crash issues while using a VBA script to lock/unlock cell based on other cell value. Could you help me to find out the error/mistake in my VBA code!

Private Sub Worksheet_Change(ByVal Target As Range)
    Unprotect "****"
    On Error Resume Next
    If [S9] = "Yes" Then
        Unprotect "****"
        [T9].Locked = False
        Protect "****"
    Else
        Unprotect "****"
        [T9].Locked = True
        [T9].ClearContents
        Protect "****"
    End If

    If [S11] = "Yes" Then
        Unprotect "****"
        [T11].Locked = False
        Protect "****"
    Else
        Unprotect "****"
        [T11].Locked = True
        [T11].ClearContents
        Protect "****"
    End If
    Protect "****"
    End Sub

Added to solve the issue but it didn't help. – sheetal singh Jan 23 '18 at 17:45 — sheetal singh, Jan 23 '18 at 17:45

score 1 · Accepted Answer · answered Jan 23 '18 at 17:49

I can see many things that can go wrong with the code you wrote depending on the circumstances that cause Worksheet_Change to be fired. Try the refactored code below which should handle (by design) most undesirable issues:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Select Case Target.Address 'only do this for particular cells, ignore other cell changes

        Case Is = "$S$9", "$S$11" 'case statement because same pattern exists for both cells

            Unprotect "****"

            If Target.Value = "YES" Then
                Target.Offset(, 1).Locked = False
            Else
                With Target.Offset(, 1)
                    Application.EnableEvents = False 'so code does not fire an infinite loop
                    .ClearContents
                    Application.EnableEvents = True
                    .Locked = True
                End With
            End If

            Protect "****"

    End Select

End Sub

Excel 2013 stop working when using VBA to lock/unlock cell

1 Answers1