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

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 2

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.