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

问题描述

我是该论坛的首次用户.这是我的情况:在一个用户窗体上，我有一个组合框，两个文本框和一个确定"按钮.当用户从组合框的下拉列表中进行选择时，将触发组合框的change事件，并且事件处理代码会根据用户的选择将工作表中的信息填充到文本框中.然后，用户可以在一个或两个文本框中编辑信息.然后，用户点击确定".然后，单击OK按钮的click事件，将修改后的信息从文本框中写回到工作表中的单元格中.似乎很简单.这是我的问题:组合框的change事件似乎在每次引用其属性时触发.具体来说，下面的cb_CustomersUpdateOK_Click()子例程中的三个实例引用了组合框的ListIndex属性.我在更改"事件代码中放置了一个Msgbox，以指示事件何时触发.在OK单击事件代码中的三个assign语句中的每个语句处使用断点，组合框将在三个语句中的每个语句处触发(显示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

推荐答案

组合框项目的来源是什么?如果链接到范围，则会触发change事件，因为写入工作表会更改链接的源，这反过来又意味着组合框会刷新自身.

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