Worksheet_Change(ByVal Target as Range) doesn't work-CodePudding

Probably I'm missing something but the most simple "Worksheet_Change" doesn't work at all. The code is placed in the correct worksheet and is exactly the same as in samples I have found in my search to solve the problem. Please ignore the lines that start with "'" as they will be part of the code after the issue is solved. I've simplified the code in trying to localize the issue but as I said, even this simple code doesn't work, even if I use a new Workbook with only one sheet. As said, I might be missing something completely simple but for the moment, I don't know what it is. Thank you.

Jan

Private Sub Worksheet_Change(ByVal Target As Excel.Range)


'    Dim KeyCells As Range

'    Set KeyCells = Range("Orders[Nieuw]")
    
'    If Not Application.Intersect(Range("Orders[Nieuw]"), Target) _
'       Is Nothing Then
'    If Target.Address = "$C$10" Then

Application.EnableEvents = False

'    If Target.Value = "Ja" Then

MsgBox "This Code Runs When Cell A1 Changes!"

Target.Font.ColorIndex = 5


'    Range("AG2").Select
'    Selection.Copy
    
'    Range("Ordernummerbereik").Select
'    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
'        :=False, Transpose:=False
        
'    SendKeys ("{ESC}")
'    End If

Application.EnableEvents = True

'    End If

End Sub

CodePudding user response：

You probably ended up in EnableEvents = False and therefore no events will be executed anymore including the Worksheet_Change event.

When using Application.EnableEvents = False make sure you use proper error handling that returns it to True otherwise if an error occurs it stays False until you close Excel.

Example for error handling

Public Sub Example()

    Application.EnableEvents = False
    On Error Goto EventsOn
    
    ' your code here


    
EventsOn:
    Application.EnableEvents = True
    If Err.Number Then Err.Raise Err.Number  ' show the error message

End Sub

That means if any error occurs in your code it jumps to EventsOn: turns events on and throws the error message. This way you ensure that Events are always turned on in case of an error.

CodePudding user response：

The only way I can reproduce your problem is by setting EnableEvents to false outside of the sub. Perhaps it crashed at some stage leaving events disabled?

Incidentally you can replace the copy and paste with

Range("Ordernummerbereik").Value = Range("AG2").Value

which is much more robust

Finally I advise you to use fully qualified ranges for ranges without an explicit name e.g. ThisWorkBook.Sheets("Sheet1").Range("A1") rather than Range("A1") You can use (note the dot-Range on AG2)

With ThisWorkBook.Sheets("Sheet1")

   Range("Ordernummerbereik").Value = .Range("AG2").Value
   ' ... other code with range references
End With