从组合框（表单控件）excel VBA 中检索所选选项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24506130/
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
Retrieve selected option from combobox(form control) excel VBA
提问by dhruva_04
Hey I don't seem to understand why my code isn't working as I saw this as an answer to another question in SO. I want to retrieve the selected item from the combobox, as I subsequently have to use it in a match index function. Here is my code
嘿，我似乎不明白为什么我的代码不起作用，因为我认为这是对 SO 中另一个问题的答案。我想从组合框中检索所选项目，因为我随后必须在匹配索引函数中使用它。这是我的代码
Option Explicit
Dim ws As Sheets
Sub test2()
Set ws = Sheets(Array("Sheet1", "Sheet2"))
With ws(1).Shapes("Drop Down 2").ControlFormat
.List(.ControlFormat.ListIndex) = ws(1).Range("I8").Value
End With
End Sub
Also, I wanted to know about how to refer to dropdowns in general? Because I have 10 such comboboxes(dropdowns) each named differently on a numerical basis. So, instead of referring to a specific dropdown like "Drop Down 2" or through using a loop say ("Drop Down " & i), is there a generic way to refer to dropdowns on a particular sheet? I really need help..
另外，我想知道一般如何引用下拉列表？因为我有 10 个这样的组合框（下拉菜单），每个组合框都以数字命名。因此，不是指特定的下拉菜单，如“下拉 2”或通过使用循环说（“下拉”和 i），是否有一种通用的方式来引用特定工作表上的下拉菜单？我真的需要帮助..
采纳答案by L42
This is how you retrieve the selected item value:
这是您检索所选项目值的方式：
Dim myindex As Long, myitem As String
Dim ws As Worksheet
Set ws = Sheets("Sheet1")
'~~> Currently selected item index at runtime
myindex = ws.Shapes("Drop Down 1").ControlFormat.Value
'~~> Currently selected item value at runtime
myitem = ws.Shapes("Drop Down 1").ControlFormat.List(myindex)
For your second question, you can use the Shape Collection Object.
Then use For Eachloop construct.
对于您的第二个问题，您可以使用Shape Collection Object。
然后使用For Each循环结构。
Dim shp As Shape, ws As Worksheet: Set ws = Sheets("Sheet1")
Dim myindex As Long, myitem As String
'~~> Iterate the shapes collection object
For Each shp In ws.Shapes
'~~> Check the type
If shp.Type = msoFormControl Then
myindex = shp.ControlFormat.Value
myitem = shp.ControlFormat.List(myindex)
'~~> additional codes here
End If
Next
But if you need to do specific stuff in specific ComboBoxes, use what you described in your question. HTH
但是，如果您需要在特定ComboBoxes 中执行特定操作，请使用您在问题中描述的内容。HTH
Edit1:
编辑1：
For Each shp In ws.Shapes
'~~> Check the type
If shp.Type = msoFormControl Then
With shp.ControlFormat
myvalue = .List(.ListIndex)
End With
End If
Next
Above works as well as you commented.
As to why it works only under With Clauseis because that is basically why you use With.
To somehow shorten the code. If you want to do it without With, use below:
上面的效果和你评论的一样。
至于为什么它只在With 子句下有效是因为这基本上就是你使用With 的原因。
以某种方式缩短代码。如果您想在没有With 的情况下执行此操作，请使用以下内容：
myvalue = shp.ControlFormat.List(shp.ControlFormat.ListIndex)
