Worksheet_Change事件未触发 [英] Worksheet_Change Event not firing

问题描述

我的Excel项目可以在家里正常运行(使用 Excel 2010 )，但是不能在两台工作计算机上运行(使用 Excel 2016 )，并且我怀疑Worksheet_Change事件是问题.

当用户进行更改时，黄色条(在屏幕截图中)应再次变为白色，但事实并非如此.我在2台工作计算机上收到2种不同的回复.

代码中有两点要指出:

1. 在某些地方，我使用vbColor扩展名，在其他地方，我必须使用数字代码.

2. 一台计算机根本不触发Worksheet_Change事件.我会注意到change事件位于代码的顶部，尽管这与它无关.

我很感谢您的建议和详细的解释，以帮助我学习.

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

解决方案

可能有许多因素导致此问题.诊断的一种方法是像这样进行故障排除:

在过程开始时，在此行之后:

Private Sub Worksheet_Change(ByVal Target As Range)

...添加临时行:

MsgBox "Changed: " & Target.Address

...然后在工作表中进行更改(任何更改都不会触发您期望的事件).

将会发生两件事之一:

1. 您将弹出一个消息框，显示刚刚更改的内容的单元格引用.
   这表明事件触发正确，因此问题必须出在随后的代码中.

2. 或者，您不会弹出消息框.这表明该事件是非触发，可能是由以下几种可能性引起的:

   • 工作簿中是否完全禁用了宏?这通常是在从外部来源收到的工作簿上自动完成的.将工作簿保存到本地计算机或网络上的受信任位置(而不是从电子邮件中打开).代码的其他部分是否正常运行?当您关闭/重新打开文件时，是否收到有关宏安全性"的警告?另外，尝试重新启动计算机.

   • 其他安全设置可能是一个问题.您曾经在这些计算机上运行过VBA吗?您可以在以下位置确认代码能够在Excel的安全设置中运行:
     文件→选项→信任中心→信任中心设置→宏设置

     除了确保在其中启用了宏之外，您还可以在信任中心中检查受信任的位置，然后将文档保存在列出的位置或添加新的位置.保存在那些位置的文档的安全性设置将被降低".

   • EnableEvents是否在代码的其他地方被故意禁用?如果编写了所有代码，则应该知道是否在某个时候设置了EnableEvents = False.也许是故意的，但并未重新启用它.

请记住要删除您临时添加的行，否则MsgBox将在每次进行更改时弹出，从而很快变得令人讨厌. :)

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

解决方案

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

这篇关于Worksheet_Change事件未触发的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！