Upgrading from Excel 2003 to 2007 results in "error" on previously working vba
The following VBA code works fine in Excel 2003, but results in an error in Excel 2007. The code is required to unlock or lock specific cells based on a dropdown menu selection. I need to be able to run the code in 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
Stack overflow almost certainly comes from recursion. Not sure why you are not getting Stack Overflow in Excel 2003 - the error might be occurring before Stack Overflow.
You can protect against infinite recursion with 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
source to share
Your method is called every time any cell in the sheet changes. Your NO block changes the content of the target cells, which causes the method to be called again.
Parameters:
- Use the flag variable Joe suggested to make sure you are already doing
-
Check 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
source to share