vba 动态构建下拉列表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11012619/
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
building dropdown lists dynamically?
提问by mezamorphic
I need to build a drop down list dynamically, whereby after entering particular text into a cell I then execute some SQL and build a Dropdownfrom the returned rows.
我需要动态构建一个下拉列表，在将特定文本输入到单元格后，我然后执行一些 SQL 并Dropdown从返回的行构建一个。
How is the event concentrated on the value of just one cell (rather than the whole spreadsheet) done?
事件如何集中在一个单元格（而不是整个电子表格）的值上？
Must I "paste" the SQL row values onto a spreadsheet before I create the Dropdown? Is it possible in VBA to populate the Dropdownwithout having to paste values onto a spreadsheet and then highlight them to create the Dropdown?
在创建之前，我必须将 SQL 行值“粘贴”到电子表格上Dropdown吗？是否可以在 VBA 中填充 ，Dropdown而不必将值粘贴到电子表格上，然后突出显示它们以创建Dropdown?
Thanks
谢谢
回答by Siddharth Rout
No it is not necessary to paste values in the sheet to create the dropdown. See this example
不，没有必要在工作表中粘贴值来创建下拉列表。看这个例子
Option Explicit
Sub Sample()
Dim dvList As String
'~~> You can construct this list from your database
dvList = "Option1, Option2, Option3"
'~~> Creates the list in Sheet1, A1
With Sheets("Sheet1").Range("A1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=dvList
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub
