为什么（Excel VBA）组合框更改事件触发每次其属性被重新启动？ [英] Why is a (Excel VBA) combobox change event triggering every time one of its properties is refferenced?

问题描述

我是这个论坛的第一个用户。这是我的场景：在用户窗体中，我有一个组合框，两个文本框和一个确定按钮。当用户从组合框的下拉列表中进行选择时，会触发组合框的更改事件，并且事件处理代码将根据用户选择从工作表中填入包含信息的文本框。然后，用户可以在一个或两个文本框中编辑信息。用户然后点击确定。 OK按钮的点击事件然后将修改的信息从文本框中写回工作表中的单元格。显得相当直截了当。这是我的问题：组合框的更改事件似乎每次引用其属性时都会触发。具体来说，cb_CustomersUpdateOK_Click（）子例程中的三个实例在下面引用了组合框的ListIndex属性。我将一个Msgbox放在Change事件代码中，以指示事件何时触发。在OK单击事件代码中的三个赋值语句的每一个上使用断点，在三个语句中的每一个上触发（Msgbox显示）组合框。当触发器发生时，它将使用组合框选择中的初始数据覆盖文本框中编辑的信息。 （1）为什么组合框更改事件触发方式？ （2）我应该怎么做才能防止这种情况发生？

I'm a first time user on this forum. Here's my scenario: On a userform, I have a combobox, two textboxes and an 'OK' button. When the user makes a makes a selection from the combobox's dropdown list, the combobox's change event is triggered and the event handling code fills in the textboxes with info from a worksheet based on the user selection. The user can then edit the information in one or both of the textboxes. The user then hits 'OK'. The OK button's click event then writes the modified info from the textboxes back to the cells in the worksheet. Appears to be fairly straight forward. Here's my problem: the combobox's change event appears to trigger every time its properties are referenced. Specifically, the three instances in the cb_CustomersUpdateOK_Click() subroutine below where the ListIndex property of the combobox is referenced. I placed a Msgbox in the Change event code to indicate when the event triggered. Using breakpoints at each of the three assign statements in the OK click event code, the combobox triggered (Msgbox displayed) at each of the three statements. When the trigger occurs, it overwrites the edited info in the textboxes with the initial data from the combobox selection. (1) Why is the combobox change event triggering the way it is? (2) What should I be doing to prevent this from happening?

我在过去几个小时内一直在研究这个问题，我没有发现任何非常有用的东西。任何帮助将不胜感激。如果需要更多信息，请让我知道。

I have been researching this for the past several hours and I haven't found anything terribly useful. Any help would be greatly appreciated. Please let me know if more info is needed.

Private Sub combo_CustomersUpdateLastName_Change()

    MsgBox "combobox changed"       'For debug purposes

    With Sheets("Customers")
        tb_CustomersUpdateFirstName.Value = .Cells(combo_CustomersUpdateLastName.ListIndex + 2, 2).Value
        tb_CustomersUpdatePhone.Value = .Cells(combo_CustomersUpdateLastName.ListIndex + 2, 3).Value
    End With

End Sub

...

Private Sub cb_CustomersUpdateOK_Click()

    'Copy the updated customer data from the controls to the Customers sheet
    With Sheets("Customers")
        .Cells(combo_CustomersUpdateLastName.ListIndex + 2, 1).Value = combo_CustomersUpdateLastName.Value
        .Cells(combo_CustomersUpdateLastName.ListIndex + 2, 2).Value = tb_CustomersUpdateFirstName.Value
        .Cells(combo_CustomersUpdateLastName.ListIndex + 2, 3).Value = tb_CustomersUpdatePhone.Value

        'Sort the customer data in case the last name or first name was updated
        Range("CustomerInfo").Sort Key1:=.Columns("A"), Key2:=.Columns("B")
    End With

    MsgBox "Customer data updated."

    Unload form_CustomersUpdate

End Sub

推荐答案

组合框项目的来源是什么？如果它链接到一个范围，则更改事件被触发，因为写入工作表会更改链接的源，这反过来意味着组合框自行刷新。

What is the source for the combobox items? If it is linked to a range then the change event is triggered because writing to the worksheet changes the linked source which in turn means the combobox refreshes itself.

如果不想要发生这种情况，那么您将需要将范围中的值缓存到数组中，并使用它们填充combox。

If you do not want this to happen then you will need to 'cache' the values from the range into an array and populate the combox with them.

这篇关于为什么（Excel VBA）组合框更改事件触发每次其属性被重新启动？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！