PDA

View Full Version : [SOLVED] All VBA Worksheet_Change Events Not Working



simora
07-31-2019, 05:22 AM
Does anyone have any idea why suddenly, ALL of my Worksheet_Change events on my worksheets stopped working.
This is some sample code that I had used. It worked once, but after I entered another value into another cell, it wouldn't fire the Worksheet_Change event code.
Other projects with previously working code stopped working also.

I tried it both with & without Application.EnableEvents = True



Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = True

If Not Intersect(Target, Target.Worksheet.Range("A1:Z53")) Is Nothing Then
MsgBox "A Cell changed"
End If
End Sub

Artik
07-31-2019, 05:29 AM
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Target.Worksheet.Range("A1:Z53")) Is Nothing Then
Application.EnableEvents = False 'If you turn off the events...
MsgBox "A Cell changed"
Application.EnableEvents = True '... you absolutely need to turn it on
End If
End Sub
Artik

Kenneth Hobs
07-31-2019, 07:02 AM
Likely, you had an error and did not catch the error in the code and re-enable events again. Rather than closing and re-opening Excel, you can easily fix that by pasting this into your VBE's Immediate window and pressing Enter key.

Application.EnableEvents = True

Here is how I would do your Change event.

Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Range

Set r = Intersect(Target, Range("A1:Z53"))
If r Is Nothing Then Exit Sub

Application.EnableEvents = False
MsgBox "A Cell changed in: " & r.Address
Application.EnableEvents = True
End Sub

simora
07-31-2019, 02:04 PM
Hi:
I had previously tried both of your suggestions, along with some other ideas.
I decided to take a fresh look at it today. Turned the system on and everything worked as it should.
I never did figure out what was causing the problem. Still not sure.
Thanks for both of your ideas.