VBA（Excel）ActiveX ListBox更改事件递归行为 [英] VBA (Excel) ActiveX ListBox Change Event recursive behaviour

问题描述

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

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:

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

但是，将所选属性设置为False似乎会触发列表框中的更改事件，这有效地成为无限循环，并导致Excel（2007）崩溃。鉴于我们知道我们也尝试了两个条目：

However, it seemed that setting the Selected property to False triggers a Change event 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:

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

这样可行！虽然我们期待同样的行为 - 设置所选属性会导致Change事件再次触发并获得无限循环。

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

但是，似乎一次例如 ListBox1.Selected（0）= False 更改事件被重新触发，但在该迭代中，它不会重新触发此行 - 我猜是因为它知道这个已选择这个项目的财产已经设置为 False ，所以没有任何变化。

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

但是如果是这样，那么我们也会期望在第一个解决方案中的行为..所以看起来有一些区别，表示 ListBox1.Selected（i）= False ，而不是直接指定实际项目索引（而不是通过变量我）。

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

有没有人知道这个行为的原因？希望这个问题是有道理的，我尽可能地解释它。

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

感谢
Amit

Thanks Amit

推荐答案

我晚了一晚，但我希望这会帮助别人。我遇到 Listbox1_Click（）无限循环而不是 change（）的问题。但是，我认为这可以是一个可行的解决方案。

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.Selected（i）= True 时，它会将其触发为 Click（）或 Change（）。在我的点击（）例程中，有一些索引将导致整个列表重新填充自己的新列表，并且重新选择本身。当它重新选择时，这将导致无限循环。我花了一天的时间进行故障排除，但最终解决方案是不要使用 click（）事件;相反，我用一点计算使用了 MouseDown（）事件。这样就消除了使用 click（）。注意到我在一个选择的列表框中使用它，而不是多选列表框。您可以使用带布尔值的 If 语句将其应用于多重选择。 Goodluck！

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 reselect itself. 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 If statement with a boolean to apply it to multiselect. Goodluck!

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

这篇关于VBA（Excel）ActiveX ListBox更改事件递归行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！