All VBA Worksheet_Change Events Not Working

[simora]

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]

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]

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]

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.

[ilcmdcv1234]

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.

What's worked for me are these steps to recompile the vba project:

Go to a blank excel worksheet. Disable macros without notification
Disable trusted locations and trusted publishers.
Go to the workbook in question and open VBA
Click debug in the top ribbon and select "Compile VBA project"
Save the project and close the workbook
Reverse steps 1-4 and open the workbook.
Everything should work as before.

[Paul_Hossler]

Welcome to the board

Please take a minute to review the FAQ link in my sig

It's also a good to check the age of the post that you're replying to since many times the post is old and not an issue anymore; many times posters will not mark the issue [SOLVED]

[ilcmdcv1234]

Welcome to the board

Please take a minute to review the FAQ link in my sig

It's also a good to check the age of the post that you're replying to since many times the post is old and not an issue anymore; many times posters will not mark the issue [SOLVED]

Thanks Paul. I only responded, because although it was marked “SOLVED”, no solution had actually been offered that worked. In the future I will reply to more recent posts. Thanks!