VBA (Excel) ActiveX ListBox 更改事件递归行为

VBA (Excel) ActiveX ListBox Change Event recursive behaviour

提问by user555265

I'm not a VBA programmer so I apologize in advance if some of my terminology in this question is incorrect. A colleague of mine wanted to clear the selection from a list box as soon as it was selected. After some googling we found one way to do it was via the Change event. Initially we tried:

我不是 VBA 程序员，所以如果我在这个问题中的一些术语不正确，我提前道歉。我的一位同事想在选择后立即清除列表框中的选择。经过一番谷歌搜索后，我们发现一种方法是通过 Change 事件。最初我们尝试：

Private Sub ListBox1_Change()
    For i = 0 To ListBox1.ListCount - 1
        ListBox1.Selected(i) = False
    Next i
End Sub

However, it seemed that setting the Selectedproperty to False triggers a Changeevent on the list box and this effectively becomes an infinite loop and causes Excel (2007) to crash. Given we knew there were two entries we also tried:

但是，似乎将Selected属性设置为 False 会触发列表框上的Change事件，这实际上变成了一个无限循环并导致 Excel (2007) 崩溃。鉴于我们知道我们还尝试了两个条目：

Private Sub ListBox1_Change()
    ListBox1.Selected(0) = False
    ListBox1.Selected(1) = False
End Sub

And that works! Though we'd expect the same behaviour - for the setting of the Selectedproperty to cause the Change event to trigger again and to get an infinite loop.

这有效！尽管我们期望相同的行为 - 对于Selected属性的设置会导致 Change 事件再次触发并获得无限循环。

However it seems that once e.g. ListBox1.Selected(0) = Falsethe Change event is re-triggered but in that iteration it doesn't retrigger on this line - i guess because it knows that this Selectedproperty has already been set to to Falsefor this item, so nothing is changing.

然而，似乎一旦例如ListBox1.Selected(0) = FalseChange 事件被重新触发，但在该迭代中它不会在这一行重新触发 - 我猜是因为它知道这个Selected属性已经被设置为False对于这个项目，所以没有任何改变。

But if that is the case then we'd also expect that behaviour in the firstsolution .. so it seems there is some difference in saying ListBox1.Selected(i) = Falseversus specifying the actual item index directly (rather than via the variable i).

但如果是这种情况，那么我们也希望第一个解决方案中的行为......所以看起来ListBox1.Selected(i) = False与直接指定实际项目索引（而不是通过变量我）。

Does anyone know the reason for this behaviour? Hope the question makes sense i've tried to explain it as best I can.

有谁知道这种行为的原因？希望这个问题是有道理的，我已经尽力解释了。

Thanks Amit

谢谢阿米特

回答by Q Le

I'm a year late to the party but I hope this will help others. I was having problem with Listbox1_Click()infinite loop rather than change(). However, I think this can be a viable solution to both.

我参加聚会晚了一年，但我希望这会对其他人有所帮助。我遇到了Listbox1_Click()无限循环而不是change() 的问题。但是，我认为这对两者都是可行的解决方案。

Whenever I called Listbox1.Selected(i) = True, it would trigger it as a Click()or a Change(). In my click()routine, there are certain index that will cause the entire list to repopulate itself with a new list and reselectitself. This causes the infinite loop when it reselected itself. It took me a day to troubleshoot, but in the end the solution was not to use click()event; instead, I used MouseDown()event with a little calculation. This eliminate the use of click(). Noted that I'm using this in a single select listbox and not a multi select listbox. You can use an Ifstatement with a boolean to apply it to multiselect. Goodluck!

每当我调用Listbox1.Selected(i) = True 时，它都会将其触发为Click()或Change()。在我的click()例程中，有某些索引会导致整个列表用新列表重新填充并重新选择自身。当它重新选择自己时，这会导致无限循环。花了一天的时间来排查，但最终的解决办法是不使用click()事件；相反，我使用MouseDown()事件进行了一些计算。这消除了click()的使用。请注意，我在单选列表框中使用它，而不是在多选列表框中使用它。您可以使用If带有布尔值的语句以将其应用于多选。祝你好运！

Private Sub ListBox1_MouseDown(ByVal Button As Integer, ByVal Shift As 

Integer, ByVal x As Single, ByVal Y As Single)

    On Error Resume Next  'You can comment this out for trouble shooting
    If Button = 1 And UBound(ListBox1.List) <> -1 Then
        ListBox1.Selected(((Y / 9.75) - 0.5) + ListBox1.TopIndex) = True
        MsgBox "left Click"
        'You can use Button = 2 for right click
        'Do some other stuff including listbox1.select(1234)

    End If
End Sub

回答by gNerb

Attach your loop to the click event instead of the change event to stop the infinite loops:

将循环附加到单击事件而不是更改事件以停止无限循环：

Private Sub ListBox1_Click()
'Do the stuff you want to do based on the selection

'Clear selections
For i = 0 To ListBox1.ListCount - 1
    ListBox1.Selected(i) = False
Next i
End Sub

I do not believe the "why" of this question has an easy answer. Generally, word is very linear and can only process 1 thing at a time. Modern processors however, optimize code and run things in tandem. Word simply gets confused in predictable enough ways to provide relatively consistent workarounds for the confusion. The trick you mentioned above (doing it twice) is one popular way to handle this issue (I saw many reccomendations back when I had the same issue with list boxes).

我不相信这个问题的“为什么”有一个简单的答案。一般来说，词是非常线性的，一次只能处理一件事。然而，现代处理器优化代码并同时运行。Word 只是以足够可预测的方式混淆，以便为混淆提供相对一致的解决方法。你上面提到的技巧（做两次）是处理这个问题的一种流行方法（当我遇到同样的列表框问题时，我看到了很多推荐）。

I prefer to enhance the logic rather then attempt a sloppy work around. As above, rather then turn off events or use the double trick, I simply realized that the change event was not the correct event to use.

我更喜欢增强逻辑而不是尝试草率的解决方法。如上所述，与其关闭事件或使用双重技巧，我只是意识到更改事件不是要使用的正确事件。

回答by Chrismas007

I would have to do more research on this, but I do know that during a For...Nextloop, once the code hits the Nextline it adds 1 to the variable (assuming you haven't defined a Step) and then runs a logic test to determine if the variable is still between your inclusive For blahconditions. Because each of these runs independently, it allows the code to check for your _Change()event. Some great workarounds for preventing an infinite loop: Change() Event Infinite Loops

我将不得不对此进行更多研究，但我确实知道在For...Next循环期间，一旦代码到达该Next行，它就会将 1 添加到变量（假设您尚未定义 a Step），然后运行逻辑测试以确定是否变量仍然在您的包容For blah条件之间。因为这些中的每一个都独立运行，所以它允许代码检查您的_Change()事件。防止无限循环的一些很好的解决方法：Change() Event Infinite Loops

EDIT: Here is some additional information on what causes the trigger ListBox.Change Documentation. It mentions that

编辑：这里是一些关于导致触发器ListBox.Change Documentation 的附加信息。它提到

Also, a programmatic change to ListBox.ListIndex also triggers the Change event.

此外，对 ListBox.ListIndex 的编程更改也会触发 Change 事件。

which might be what is being checked between loops.

这可能是循环之间正在检查的内容。

回答by Jordi

You are right about using the Change event, but it's somehow tricky in ListBoxes. You're obviously triggering the Change event every time you deselect an element, because you are changingthe ListBox from code.

您使用 Change 事件是正确的，但在 ListBoxes 中它有点棘手。每次取消选择一个元素时，您显然都会触发 Change 事件，因为您正在通过代码更改ListBox。

The answer from guitarthrowergoes in the good direction, but I don't think that Application.EnableEventswould do the job in this case as it does not have any effect on ActiveX objects.

来自guitarthrower的答案朝着好的方向发展，但我认为Application.EnableEvents在这种情况下不会起作用，因为它对ActiveX对象没有任何影响。

So, I would try this two alternatives which try to emulate the job of Application.EnableEventsby plain programming:

所以，我会尝试这两种尝试Application.EnableEvents通过普通编程来模拟工作的替代方案：

• Alternative 1: general solution for the Change event. However, it's a bit risky if the NoExecutevariable is not reseted by any reason (function exiting due to an error).

  Private NoExecute As Boolean
  
  Private Sub ListBox1_Change()
  
      If NoExecute Then Exit Sub
  
      NoExecute = True
  
      For i = 0 To ListBox1.ListCount - 1
          ListBox1.Selected(i) = False
      Next
  
      NoExecute = False
  
  End Sub
  

• Alternative 2: best solution in this case, although not as pretty as the previous one.

  Private Sub ListBox1_Change()
      For i = 0 To ListBox1.ListCount - 1
          If ListBox1.Selected(i) Then
              ListBox1.Selected(i) = False
          End If
      Next
  End Sub        
  

• 备选方案 1：Change 事件的通用解决方案。但是，如果NoExecute变量没有因任何原因重置（函数因错误退出），则有点冒险。

  Private NoExecute As Boolean
  
  Private Sub ListBox1_Change()
  
      If NoExecute Then Exit Sub
  
      NoExecute = True
  
      For i = 0 To ListBox1.ListCount - 1
          ListBox1.Selected(i) = False
      Next
  
      NoExecute = False
  
  End Sub
  

• 备选方案 2：在这种情况下的最佳解决方案，尽管不如前一个方案漂亮。

  Private Sub ListBox1_Change()
      For i = 0 To ListBox1.ListCount - 1
          If ListBox1.Selected(i) Then
              ListBox1.Selected(i) = False
          End If
      Next
  End Sub        
  

Let me know wether it worked.

让我知道它是否有效。

This site describes Alternative 1 in depth: http://www.cpearson.com/excel/SuppressChangeInForms.htm

该站点深入描述了备选方案 1：http: //www.cpearson.com/excel/SuppressChangeInForms.htm

回答by Han Soalone

To stop the infinite insanity, you can do this (tough even better question is, why would you do this anyway, because after this no value can be selected or actually can be, but it's immediately changed back to false) -

为了停止无限的疯狂，你可以这样做（更棘手的问题是，你为什么要这样做，因为在此之后没有值可以被选择或实际上可以被选择，但它会立即变回假） -

Private Sub ListBox1_Change()
    For i = 0 To ListBox1.ListCount - 1 
         if ListBox1.Selected(i) = true then ListBox1.Selected(i) = false 
    Next i
End Sub

Now the reason for the infinite loop is not visible and one can only guess, but first lets correct the assumption that the code block without the for next loop would not cause the same issue. It actually does the same thing as the first one and get stuck in an infinite loop, if you change the value of the ListBox1.Selected(1) or later..

现在无限循环的原因是不可见的，只能猜测，但首先让我们纠正没有 for next 循环的代码块不会导致同样问题的假设。如果您更改 ListBox1.Selected(1) 或更高版本的值，它实际上与第一个执行相同的操作并陷入无限循环。

Why this happens is the change event will be triggered right after the first value is changed, and as the first cell is changed the change event gets triggered and code execution is stopped for that instance and begins again. Now both of the codes work without causing a loop if only the first item is changed and why this happens is probably for the compiler kicks in with it's automatics. I can't tell you the real reason behind the happenings, but what probably happens is the compiler predicts when only the first value is changed and optimizes the code to do only the one change and nothing else and when other values change it is trying to rewrite every value and the first value every time and again the infinite loop is triggered.

发生这种情况的原因是更改事件将在第一个值更改后立即触发，并且随着第一个单元格的更改，更改事件被触发并且该实例的代码执行停止并再次开始。现在，如果仅更改第一项，则这两个代码都可以正常工作而不会导致循环，并且发生这种情况的原因可能是编译器会自动启动。我不能告诉你这些事情背后的真正原因，但可能发生的事情是编译器预测何时只更改第一个值并优化代码以仅执行一个更改而不执行其他任何更改，并且当其他值更改时它试图一次又一次地重写每个值和第一个值，无限循环被触发。

That's just how it is, to get a full understand what is happening under the hood, you'd better contact MS.

就是这样，要全面了解幕后发生的事情，您最好联系 MS。

回答by Marcus Mangelsdorf

Just for future reference: If you just want to disable selectionin a ListBoxcontrol you can either use

仅供将来参考：如果您只想禁用ListBox控件中的选择，您可以使用

ListBox.Enabled = False

or

或者

ListBox.Locked = True

or both, of course.

当然，或者两者兼而有之。

You can find details on the difference in behaviour on MSDN.
Basically it's supposed to be like this (directly from the linked MSDN article above):

您可以在MSDN上找到有关行为差异的详细信息。
基本上它应该是这样的（直接来自上面链接的 MSDN 文章）：

• If Enabledand Lockedare both True, the control can receive focus and appears normally (not dimmed) in the form. The user can copy, but not edit, data in the control.
• If Enabledis Trueand Lockedis False, the control can receive focus and appears normally in the form. The user can copy and edit data in the control.
• If Enabledis Falseand Lockedis True, the control cannot receive focus and is dimmed in the form. The user can neither copy nor edit data in the control.
• If Enabledand Lockedare both False, the control cannot receive focus and is dimmed in the form. The user can neither copy nor edit data in the control.

• 如果Enabled和Locked都为True，则控件可以接收焦点并在表单中正常显示（不变暗）。用户可以复制但不能编辑控件中的数据。
• 如果Enabled为True且Locked为False，则控件可以接收焦点并在表单中正常显示。用户可以复制和编辑控件中的数据。
• 如果Enabled为False且Locked为True，则控件无法接收焦点并在窗体中变暗。用户不能复制或编辑控件中的数据。
• 如果Enabled和Locked都为False，则控件无法获得焦点并在窗体中变暗。用户不能复制或编辑控件中的数据。

Note that I'm not sure about the behaviour in Office 2007 since I tried your example with Office 2013 and get the exact same behaviour for both code snippets. And they both don'tprevent the selection but rather do "nothing" (well, they get called two times but the change doesn't affect the displayed selection).

请注意，我不确定 Office 2007 中的行为，因为我在 Office 2013 中尝试了您的示例并获得了两个代码片段完全相同的行为。而且它们都不会阻止选择，而是“什么都不做”（好吧，它们被调用了两次，但更改不会影响显示的选择）。

And during my experiments I also noticed, that the ListBox_Changeevent gets called before the ListBox_Clickevent.

在我的实验中，我还注意到，ListBox_Change事件在ListBox_Click事件发生之前被调用。