使用 VBA 以 Excel 形式填充动态下拉列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21849133/
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
Populate dynamic dropdown in Excel form with VBA
提问by Shweta Bet
I have a dropdown on an Excel form which I want to populate with items listed in sheet "OR", however I need only unique entries from this dynamic list & then populate the dropdown.
我在 Excel 表单上有一个下拉列表，我想用工作表“OR”中列出的项目填充它，但是我只需要这个动态列表中的唯一条目，然后填充下拉列表。
Have searched many blogs, suggesting named ranges as Rowsource property, but my list have duplicate items & is dynamic.
搜索了很多博客，建议将命名范围作为 Rowsource 属性，但我的列表有重复项并且是动态的。
采纳答案by CRondao
You can write this code:
您可以编写以下代码：
Dim s As String, r As Integer, nr As Integer, wr, v
Set wr = Range("A1:A10")
nr = wr.Rows.Count
For r = 1 To nr
v = wr(r, 1)
If InStr(s, v & ",") = 0 Then
s = s & v & ","
End If
Next
s = Left(s, Len(s) - 1)
With Range("D1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=s
End With
This uses data validation, I assume data is in A1:A10 and the ComboBox in D1. The ComboBox will contain only A1:A10 distinct values
这使用数据验证，我假设数据在 A1:A10 和 D1 中的 ComboBox。ComboBox 将只包含 A1:A10 不同的值
If you prefer a ComboBox ActiveX Object try this:
如果您更喜欢 ComboBox ActiveX 对象，请尝试以下操作：
Dim s As String, r As Integer, nr As Integer, wr, v
Set wr = Range("A1:A10")
nr = wr.Rows.Count
With ComboBox1
.Clear
For r = 1 To nr
v = wr(r, 1)
If InStr(s, v & ",") = 0 Then
s = s & v & ","
.AddItem (v)
End If
Next
End With
