在 VBA 中获取下拉值并获取下拉的名称...无处可寻？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3826353/
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
Get dropdown value in VBA and get the name of the dropdown...nowhere to be found?
提问by EKet
I created a dropdown by dragging the combo box onto my sheet from the UserForm toolbar. I assigned some values to it from some cells in the book. Now I want some VBA code to access the selected dropdown item's value in the form of a string.
我通过将组合框从用户窗体工具栏拖到我的工作表上来创建一个下拉列表。我从书中的一些单元格中为它分配了一些值。现在我想要一些 VBA 代码以字符串的形式访问所选下拉项的值。
My dropdown contains only text.
我的下拉列表只包含文本。
Also how do I find the name of this newly created dropdown (it's nowhere in the properties!)?
另外我如何找到这个新创建的下拉列表的名称（它在属性中无处可寻！）？
采纳答案by EKet
Dim dd As DropDown
Set dd = ActiveSheet.DropDowns("Drop Down 6")
Set r = Sheet2.Range(dd.ListFillRange)
Set ddValue = r(dd.Value)
NOTES:
笔记：
DropDown is not a visible class. You just use it and it works.
To find the name of the dropdown CONTROL(not userform) just look at
the name box in the top left corner of your screen just above column A. It says the name of the control when you right click on your control.-Sheet2 is where the dropdown list is populated. So wherever your list data is.
Hope that helps you all.
DropDown 不是可见的类。你只需使用它，它就可以工作。
要找到下拉控件的名称 （不是用户窗体），只需查看
屏幕左上角 A 列上方的名称框。当您右键单击控件时，它会显示控件的名称。-Sheet2 是填充下拉列表的位置。因此，无论您的列表数据在哪里。
希望对大家有帮助。
回答by Lance Roberts
Here's how you get the String without needing to know the name:
以下是无需知道名称即可获取字符串的方法：
Dim DD As Shape
Set DD = ActiveSheet.Shapes(Application.Caller)
MsgBox DD.ControlFormat.List(DD.ControlFormat.ListIndex)
回答by Rhys
Lance Roberts was almost there. If you don't know the name of the drop down that calls the sub, use this:
兰斯·罗伯茨几乎就在那里。如果您不知道调用 sub 的下拉列表的名称，请使用以下命令：
Dim dd as DropDown
Set dd=ActiveSheet.Shapes(Application.Caller).OLEFOrmat.Object
Dim ddVal as String
ddVal=dd.List(dd.ListIndex)
I used this to create a generic sub for a form with many drop downs.
我用它为带有许多下拉菜单的表单创建了一个通用子。
回答by barrowc
This is a clunky way of doing it but it should work:
这是一种笨拙的方法，但它应该有效：
Dim o As Object
For Each o In Worksheets("Sheet1").Shapes
MsgBox o.Name
Next o
There is also a hidden DropDownscollection member of the Worksheetobject that you could iterate over. This will find items inserted from the Formstoolbar but won't find items inserted from the Control Toolboxtoolbar
您还可以迭代对象的一个隐藏DropDowns集合成员Worksheet。这将找到从Forms工具栏插入的项目，但不会找到从Control Toolbox工具栏插入的项目
