获取组合框的选定值 [英] 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。将其公开将使它及其所有子级失去作用。您需要在范围内使用它们，以便触发事件。在该过程中，循环浏览所有组合框，为每个组合框创建一个新的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/sfj4kyzolfy0上查看示例3qe / ComboboxEvents.xlsm

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