VBA - 获取组合框的选定值 [英] VBA - Get Selected value of a Combobox
问题描述
如何获取所选的ComboBox值？当选择ComboxBox时，是否有方法触发函数（即事件处理程序）？
处理数据验证列表，可以使用Worksheet_Change事件。右键单击具有数据验证的工作表，然后选择查看代码。然后输入：
Private Sub Worksheet_Change（ByVal Target As Range）
MsgBox Target.Value
End Sub
如果您正在处理ActiveX组合框，一点点复杂您需要创建一个自定义类模块来连接事件。首先，创建一个名为CComboEvent的类模块，并将此代码放在其中。
Public WithEvents Cbx As MSForms.ComboBox
Private Sub Cbx_Change（）
MsgBox Cbx.Value
End Sub
接下来，创建另一个名为CComboEvents的类模块。这将保存我们所有的CComboEvent实例并保留它们的范围。将此代码放在CComboEvents中。
私有mcolComboEvents作为集合
私有子类Class_Initialize（）
设置mcolComboEvents =新集合
End Sub
私有子类_Terminate（）
设置mcolComboEvents = Nothing
End Sub
Public Sub Add （clsComboEvent As CComboEvent）
mcolComboEvents.Add clsComboEvent，clsComboEvent.Cbx.Name
End Sub
最后，创建一个标准模块（不是一个类模块）。您将需要代码将所有组合框放入课程模块。您可以将其放在Auto_Open过程中，以便每当打开工作簿时都会发生这种情况，但这取决于您。
您需要一个公共变量才能持有CComboEvents。使公众将kepp它和所有的孩子在范围内。您需要它们的范围，以便触发事件。在该过程中，循环遍历所有组合框，为每个组合框创建一个新的CComboEvent实例，并将其添加到CComboEvents。
public gclsComboEvents As CComboEvents
Public Sub AddCombox（）
Dim oleo As OLEObject
Dim clsComboEvent As CComboEvent
设置gclsComboEvents =新建的CComboEvents
对于每个oleo在Sheet1.OLEObjects
如果TypeName（oleo.Object）=ComboBox然后
设置clsComboEvent =新的CComboEvent
设置clsComboEvent.Cbx = oleo .Object
gclsComboEvents.Add clsComboEvent
End If
Next oleo
End Sub
现在，每当一个组合框被更改时，事件将会触发，在这个例子中，会出现一个消息框。
您可以在 https://www.dropbox.com/s/sfj4kyzolfy0上查看示例3qe / ComboboxEvents.xlsm
I have a thousands of cells in an Excel worksheet which are ComboBoxes. The user will select one at random and populate it.
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?
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
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
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
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.
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.
You can see an example at https://www.dropbox.com/s/sfj4kyzolfy03qe/ComboboxEvents.xlsm
这篇关于VBA - 获取组合框的选定值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
