VBA - 获取组合框的选定值 [英] VBA - Get Selected value of a Combobox

问题描述

我在Excel工作表中有数千个单元格是ComboBoxes。用户将随机选择一个并填充它。

I have a thousands of cells in an Excel worksheet which are ComboBoxes. The user will select one at random and populate it.

如何获取所选的ComboBox值？当选择ComboxBoxes时，是否有一种方法来触发函数（即事件处理程序）？

How do I get the selected ComboBox value? Is there a way to trigger a function (i.e. an event handler) when the ComboxBoxes has been selected?

推荐答案

处理数据验证列表，可以使用Worksheet_Change事件。右键单击带有数据验证的工作表，然后选择查看代码。然后输入：

If you're dealing with Data Validation lists, you can use the Worksheet_Change event. Right click on the sheet with the data validation and choose View Code. Then type in this:

Private Sub Worksheet_Change(ByVal Target As Range)

    MsgBox Target.Value

End Sub

如果处理ActiveX组合框，一点复杂。您需要创建一个自定义类模块来挂接事件。首先，创建一个名为CComboEvent的类模块，并将此代码放入其中。

If you're dealing with ActiveX comboboxes, it's a little more complicated. You need to create a custom class module to hook up the events. First, create a class module named CComboEvent and put this code in it.

Public WithEvents Cbx As MSForms.ComboBox

Private Sub Cbx_Change()

    MsgBox Cbx.Value

End Sub

接下来，创建另一个名为CComboEvents的类模块。这将保存所有的CComboEvent实例，并保持在范围内。将此代码放在CComboEvents中。

Next, create another class module named CComboEvents. This will hold all of our CComboEvent instances and keep them in scope. Put this code in CComboEvents.

Private mcolComboEvents As Collection

Private Sub Class_Initialize()
    Set mcolComboEvents = New Collection
End Sub

Private Sub Class_Terminate()
    Set mcolComboEvents = Nothing
End Sub

Public Sub Add(clsComboEvent As CComboEvent)

    mcolComboEvents.Add clsComboEvent, clsComboEvent.Cbx.Name

End Sub

最后，创建一个标准模块（不是类模块）。你需要代码将所有的组合框放入类模块。你可以把它放在一个Auto_Open过程中，所以每当工作簿打开时都会发生，但这是由你决定的。

Finally, create a standard module (not a class module). You'll need code to put all of your comboboxes into the class modules. You might put this in an Auto_Open procedure so it happens whenever the workbook is opened, but that's up to you.

你需要一个Public变量来保存一个实例CComboEvents。使它公开将kepp它及其所有的孩子，在范围。您需要它们在范围内，以便触发事件。在该过程中，循环遍历所有组合框，为每个组合框创建一个新的CComboEvent实例，并将其添加到CComboEvents。

You'll need a Public variable to hold an instance of CComboEvents. Making it Public will kepp it, and all of its children, in scope. You need them in scope so that the events are triggered. In the procedure, loop through all of the comboboxes, creating a new CComboEvent instance for each one, and adding that to CComboEvents.

Public gclsComboEvents As CComboEvents

Public Sub AddCombox()

    Dim oleo As OLEObject
    Dim clsComboEvent As CComboEvent

    Set gclsComboEvents = New CComboEvents

    For Each oleo In Sheet1.OLEObjects
        If TypeName(oleo.Object) = "ComboBox" Then
            Set clsComboEvent = New CComboEvent
            Set clsComboEvent.Cbx = oleo.Object
            gclsComboEvents.Add clsComboEvent
        End If
    Next oleo

End Sub

现在，无论何时更改组合框，事件都会触发，在此示例中，将显示一个消息框。

Now, whenever a combobox is changed, the event will fire and, in this example, a message box will show.

您可以在 https://www.dropbox.com/s/sfj4kyzolfy03qe/ComboboxEvents上查看示例.xlsm

这篇关于VBA - 获取组合框的选定值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！