Worksheet_Change Event not firing

Question

My Excel project functions properly at home (with Excel 2010), but not on two work computers (with Excel 2016) and I suspect the Worksheet_Change event is the problem.

When the user makes changes, the yellow bar (in the screenshot) should turn white again, but it is not. I am getting 2 different responses on 2 work computers.

Two things to point out in the code:

1. In some places I use vbColor extensions, in others I had to use a numerical code.

2. One computer is not firing the Worksheet_Change event at all. I would note that the change event is at the top of the code, although that shouldn't have anything to do with it.

I'd appreciate advice and detailed explanations, to help me learn.

Private Sub Worksheet_Change(ByVal Target As Range) 'Check for On-Time and Delays then change the Command Button Colors to show completed.  

'Return headers to white after jump to
Range("B3:I3,O3:V3,B28:I28,O28:V28,B53:I53,O53:V53,B78:I78,O78:V78,B103:I103,O103:V103,B128:I128,O128:V128,B153:I153,O153:V153").Interior.Color = vbWhite
'Check for On Time and Delayed Trips
'Trip 1 Scan Ready
If IsEmpty(Range("L3").Value) = False Then
    If Range("L3").Value > Range("I3").Value Then 'If actual is greater than Departure
        'If Delayed check for a delay code
        If IsEmpty(Range("L24").Value) Then 'If Delay code is missing
            Range("K24:L25").Interior.Color = 16711935
            CommandButton1.BackColor = 16711935
            CommandButton1.ForeColor = vbBlack
        Else 'If Delay Code is present check for delay time
            If IsEmpty(Range("L25").Value) Then
                Range("K24:L25").Interior.Color.Index = 16711935
                CommandButton1.BackColor = 16711935
                CommandButton1.ForeColor = vbBlack
            Else
                CommandButton1.BackColor = vbRed
                CommandButton1.ForeColor = vbWhite
                Range("K24:L25").Interior.Color = vbWhite
            End If
        End If
    Else
        'Flight was on Time
        CommandButton1.BackColor = 32768 '32768 = Green
        CommandButton1.ForeColor = vbWhite
        Range("K24:L25").Interior.Color = vbWhite
    End If
End If

Answer 1

Just throwing in my 2 cents. Make sure you are NOT in 'Design Mode'. The Events will not be triggered if you are.

Answer 2

I also had this issue of Worksheet_Change not firing, but it was random. I started to suspect the compiled code had become corrupt, but I found the problem. It was a silly mistake on my part.

I was debugging the code, and I left the debugger running..!

I was working on the code & making changes, and switched back to the worksheet to test-fire the event again. And - Voila! - Nothing! But of course it did not fire because the event was still stopped in the debugger from before. So I stopped the debugger and it started working again.

Answer 3

I had this issue and the problem ended up being that I had a compiling syntax error in the code. Normally when running a subroutine manually, it will give you an error telling you of the compiling error. When it is in a worksheet change event, it apparently does not let you know this.

A quick way to test if this is your issue is to click the Debug menu from the top bar then click Compile VBAProject

Answer 4

In 2019 Excel cut copy -> paste would not work the paste options were grayed out. Online sources said update and repair the app. I updated office and the copy/paste options worked.

However the worksheet_change event stopped working. After a lot debugging with no luck, I did a hail mary and commented out the subroutine and recreated it. It WORKED!! I have absolutely no idea why or how. If anyone has thoughts please share.

Answer 5

I had some code in a worksheet change that wasnt firing. I also had some code in Thisworkbook upon opening and saving. Resolved the issue by putting Application.EnableEvents = True at the end of the code for workbook open and workbook save.

Answer 6

I solved it simply deactivating "Design Mode" in the "Developer" tab. It seems if you are in "Design Mode", this event (I don't know about others) won't work.

Answer 7

I had a different problem. I had saved my worksheet under a new name and then added the code for the event. To get it to work, I had to close the worksheet and reopen it. It then showed the button to enable macros and the code started to work. Hope this helps someone.

Wade

Answer 8

I had the same problem. I checked everything. Everything seemed to be proper (enabled macros, EnableEvents=True, etc). I closed and opened Excel. Problem persisted. There was nothing I could do. I restarted Windows. Problem disappeared. Restart took 7 minutes (with all applications closing & restarting), trying to find the cause would take much more. Maybe I could have tried to find & kill every Excel process in Task Manager. I don't like giving people the advice "try rebooting", but well, Windows is Windows.

Answer 9

There could be a number of factors causing this problem. One way to diagnose is to troubleshoot like this:

At the beginning of your procedure, right after this line:

Private Sub Worksheet_Change(ByVal Target As Range)

...add a temporary line:

MsgBox "Changed: " & Target.Address

...then go change something in your worksheet (whatever change isn't firing the event as you'd expect).

One of two things will happen:

1. You'll have a message box pop up, showing the cell reference of whatever was just changed.
   This demonstrates that the event is firing properly, so the issue must be in your code that follows.

2. Or, you won't get a message box pop up. This indicates the event is not firing, which could be caused by a few possibilities:

   • Are macros completely disabled in the workbook? This is often done automatically on workbooks received from outside sources. Save the workbook to a trusted location on the local computer or network (rather than opening from the email). Do other sections of code run properly? When you close/re-open the file, are you given a warning about Macro Security? Also, try rebooting the computer.

   • Other security settings could be an issue. Have you ever run VBA on these machines? You can confirm sure code is able to run in Excels' security settings in:
     File→Options→Trust Center→Trust Center Settings→Macro Settings

     As well as making sure macros are enabled there, you could also check Trusted Locations in the Trust Center, and either save your document in a listed location, or add a new location. Security settings will be "reduced" for documents saved in those locations.

   • Is EnableEvents being intentionally disabled elsewhere in your code? If you wrote all the code, you should know whether you set EnableEvents = False at some point. Perhaps it was intentional, but it's not being re-enabled.

Remember to remove the line you added temporarily, or that MsgBox will quickly get annoying by popping up every time a change is made. :)

Answer 10

You say "the change event is at the top of the code". A worksheet change event will only fire if you put the code in the sheet module concerned. If you've put the code concerned in a non sheet module (e.g. "Module 1" or similar, listed under the "Modules" branch in the object explorer) then that's the problem.

Also, you really shouldn't hard-code cell references like "L3" in your VBA code, because every hard reference will require amending should you (or a user) later insert rows/columns above/to the left of these references. Instead, assign meaningful named ranges to these cells back in Excel, and use those in your VBA.

Also, when using event handlers like you're doing, you should have something like If not intersect(Target, InputRange) is nothing then... so that the code only runs if something of interest changes.