Upgrade from Excel 2003 to 2007 results in "stack overflow error" on previously working vba

Question

The following VBA code works great in Excel 2003, but results in a "stack overflow error" in Excel 2007. The code is required to either unlock or lock certain cells based on a drop-down menu selection. I need to be able to run the code in both Excel 2003 and 2007. Please help.

Private Sub Worksheet_Change(ByVal Target As Range)
If [E28] = "NO" Then
ActiveSheet.Unprotect ("PASSWORD")
[K47:K53].Locked = False
[K47:K53].Interior.ColorIndex = 16
[K47:K53].ClearContents
ActiveSheet.Protect ("PASSWORD")
Else
ActiveSheet.Unprotect ("PASSWORD")
[K47:K53].Interior.ColorIndex = 0
'Next line is optional, remove preceding apostrophe if protection should stay on.
ActiveSheet.Protect ("PASSWORD")
End If
End Sub

Answer 1

The stack overflow almost certainly comes from recursion. Not sure why you aren't getting a stack overflow in Excel 2003 - perhaps an error is being raised before the stack overflows.

You can protect against infinite recursion something like the following:

Private m_bInChange As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
If m_bInChange Then Exit Sub
On Error GoTo ErrHandler
    m_bInChange = True
    If [E28] = "NO" Then
        ActiveSheet.Unprotect ("PASSWORD")
        [K47:K53].Locked = False
        [K47:K53].Interior.ColorIndex = 16
        [K47:K53].ClearContents
        ActiveSheet.Protect ("PASSWORD")
    Else
        ActiveSheet.Unprotect ("PASSWORD")
        [K47:K53].Interior.ColorIndex = 0
        'Next line is optional, remove preceding apostrophe if protection should stay on.
        ActiveSheet.Protect ("PASSWORD")
    End If

    m_bInChange = False
    Exit Sub
ErrHandler:
    m_bInChange = False
    Exit Sub
End Sub

Answer 2

Your method gets called whenever ANY cell in the worksheet changes. Your "NO" block changes the contents of the target cells, which causes the method to be called again.

Options:

1. Use a flag variable as Joe suggested, to see if you're already executing

2. Test the "Target" value to see if it's E28, if that's the cell change you want to capture. Something like

   If Target.Address <> "$E$28" Then Exit Sub