VBA - 获取组合框的选定值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15535540/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA - Get Selected value of a Combobox
提问by user559142
I have a thousands of cells in an Excel worksheet which are ComboBoxes. The user will select one at random and populate it.
我在 Excel 工作表中有数千个单元格，它们是组合框。用户将随机选择一个并填充它。
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?
如何获取选定的 ComboBox 值？当 ComboxBoxes 被选中时，有没有办法触发一个函数（即事件处理程序）？
采纳答案by Dick Kusleika
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:
如果您正在处理数据验证列表，则可以使用 Worksheet_Change 事件。右键单击带有数据验证的工作表，然后选择查看代码。然后输入这个：
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.
如果您正在处理 ActiveX 组合框，那就有点复杂了。您需要创建一个自定义类模块来连接事件。首先，创建一个名为 CComboEvent 的类模块并将此代码放入其中。
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.
接下来，创建另一个名为 CComboEvents 的类模块。这将保存我们所有的 CComboEvent 实例并将它们保持在范围内。将此代码放在 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.
最后，创建一个标准模块（不是类模块）。您需要将所有组合框放入类模块的代码。您可以将它放在 Auto_Open 过程中，以便在打开工作簿时发生，但这取决于您。
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.
您需要一个公共变量来保存 CComboEvents 的实例。将其设为公开将使其及其所有子项保持在范围内。您需要在范围内使用它们，以便触发事件。在该过程中，遍历所有组合框，为每个组合框创建一个新的 CComboEvent 实例，并将其添加到 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
你可以在https://www.dropbox.com/s/sfj4kyzolfy03qe/ComboboxEvents.xlsm看到一个例子
回答by user559142
You can use the below change event to which will trigger when the combobox value will change.
您可以使用以下更改事件，当组合框值更改时将触发该事件。
Private Sub ComboBox1_Change()
'your code here
End Sub
Also you can get the selected value using below
您也可以使用以下方法获取所选值
ComboBox1.Value
回答by Fandango68
A simpler way to get the selected value from a ComboBox control is:
从 ComboBox 控件获取选定值的更简单方法是：
Private Sub myComboBox_Change()
msgbox "You selected: " + myComboBox.SelText
End Sub
回答by til_b
Maybe you'll be able to set the event handlers programmatically, using something like (pseudocode)
也许您将能够以编程方式设置事件处理程序，使用类似（伪代码）
sub myhandler(eventsource)
process(eventsource.value)
end sub
for each cell
cell.setEventHandler(myHandler)
But i dont know the syntax for achieving this in VB/VBA, or if is even possible.
但我不知道在 VB/VBA 中实现这一点的语法，或者甚至可能。
