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

Solution 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. :)

Solution 2

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.

Solution 3

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.

Comments

[0m3r]

not firing the Worksheet_Change could be security settings, for the color try using Excel RGB color example .Color = RGB(0, 0, 0)

[paul bica]

^ For firing events try running this line Application.EnableEvents = True, for the white bar place a break point on line If Range("L3").Value > Range("I3").Value and check the values for both cells (and time format)

[NWTech]

Paul do I put that line in before the first event.

[paul bica]

Create a separate Sub like this: Sub ee(): Application.EnableEvents = True: End Sub, then press F5

[ashleedawg]

@paulbica The only way Events wouldn't be enabled is if he disabled them, or am I missing something?

[paul bica]

@ashleedawg: another reason for them to be disabled is if other code that disables them runs, gets an error and doesn't get to the point where they get enabled again (or the code doesn't enable them back)

[ashleedawg]

@pica - again, he would have had to disable Events at some point in his code; I don't understand what leads you to believe he did that? i don't mean to argue - just curious what lead to that "hunch". Also - a quick way to confirm is [CTRL]+[G] then ?Application.EnableEvents and [Enter].

[jeffreyweir]

@ashleedawg That comment was meant for NWTech sorry. I've just deleted it and reposted below.

[paul bica]

@ashleedawg - no problem. I'm thinking that the code on the other computer might be created by someone else (or other utilities installed by the other user)

[ashleedawg]

@paulbica well we'll find out who's right when (if?) he returns... (Not that it's a competition!) :) I'm betting the users were opening the file directly from email and quickly clicking past the macros-disabled warning...

[Chronocidal]

@ashleedawg: I've had that issue before. In the end, I added a worksheet with big red text that said "REOPEN THE WORKBOOK AND ENABLE MACROS", and use Workbook_Open and Workbook_BeforeSave to ensure that if you had Macros enabled then the sheet was xlVeryHidden, but if you had Macros disabled then it was the only sheet that wasn't xlVeryHidden. ("U5312 error: Faulty component exists between keyboard and chair.")

[Davesexcel]

I wonder if the code is in the worksheet module.The statement of the code being at the top of the code is why I'm asking.

[ashleedawg]

...but it was working for him at home. (also I deleted my other comment, I thought you were referring to be telling him to enable events inside the event) :)

[jeffreyweir]

I'm thinking (guessing) he might have moved the location of the code in between work and home pcs.

[NWTech]

My thanks to all the comments and suggestions. We were able to find the problem. My associate at work was trying to run the file from outlook. When he actually saved the file to the computer and ran it in that manner, everything worked as planned.

[jeffreyweir]

Cool. Can you add this as an answer and not a comment, so that other folk know that the issue is resolved without having to read through all the comments? Cheers.

[NWTech]

Also, Jeff, your suggestion is what I intended. The reason that it is coded this way is because of the way that it is set up. I was as specific as I was to ensure that we only capture specific information changes. However I will say that all things being equal, I am learning as I go. Input Ranges seem to be a bit difficult for me as they cover a large range, but I have learned that I can list specific cells for the code to fire on and that works, its handling the rest of the if statement that I seem to be having difficulty with.

[jeffreyweir]

Cool. You definitely don't want to get into the habit of using "naked" cell addresses in your VBA. Excel Tables are good for this reason, as they are named ranges that Excel maintains automatically. And often I will add a named range to a table column, and reference that named range instead of the table column, just in case someone later changes that table column name.

[Selkie]

To quickly test the EnableEvents portion, you can type Application.EnableEvents = True in the immediate window, hit enter, then try again.

[Karthick Ganesan]

Application.EnableEvents was the killer for me! +1