为什么每次引用其属性之一时都会触发(Excel VBA)组合框更改事件? [英] Why is a (Excel VBA) combobox change event triggering every time one of its properties is referenced?

问题描述

我是这个论坛的第一次用户.这是我的场景:在用户窗体上，我有一个组合框、两个文本框和一个确定"按钮.当用户从组合框的下拉列表中进行选择时，组合框的更改事件被触发，事件处理代码根据用户选择使用工作表中的信息填充文本框.然后，用户可以在一个或两个文本框中编辑信息.然后用户点击确定".OK 按钮的单击事件然后将修改后的信息从文本框中写回工作表中的单元格.似乎相当直接.这是我的问题:组合框的更改事件似乎在每次引用其属性时都会触发.具体来说，下面 cb_CustomersUpdateOK_Click() 子例程中的三个实例引用了组合框的 ListIndex 属性.我在 Change 事件代码中放置了一个 Msgbox 来指示事件触发的时间.在 OK 单击事件代码中的三个分配语句中的每一个使用断点，组合框在三个语句中的每一个处触发(显示 Msgbox).当触发器发生时，它会用来自组合框选择的初始数据覆盖文本框中编辑的信息.(1)为什么combobox change事件会这样触发?(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.

如果您不希望这种情况发生，那么您需要将范围内的值缓存"到一个数组中，并用它们填充组合框.

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屋！