以编程方式创建 Excel VBA 验证列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4497854/
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
Programmatically creating Excel VBA validation list
提问by Alistair Collins
I have an array of data that is coming in to the VBA code from an external source. I want to be able to assign that data to use as a validation in a dropdown box in a cell in one of the sheets in this workbook. However, I do not want to copy that data into a sheet and then use a named range - there may be quite a lot of data, and that would not feel very efficient!
我有一组从外部源进入 VBA 代码的数据。我希望能够分配该数据以用作此工作簿中其中一张工作表中单元格下拉框中的验证。但是，我不想将该数据复制到工作表中，然后使用命名范围 - 可能有很多数据，这感觉效率不高！
I'm sure there must be a way - but I haven't found one yet. Any ideas?
我确定一定有办法——但我还没有找到。有任何想法吗？
回答by Thunder
Place the data in some text file delimiting it with comma eg(a,b,c).
Read that data using VBA into a string variable eg ValidationList.
Use some thing like this
With Range("A1").Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=ValidationList
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
将数据放在一些用逗号分隔的文本文件中，例如(a,b,c)。
使用 VBA 将该数据读入字符串变量，例如 ValidationList。
使用这样的东西
使用 Range("A1").Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=ValidationList
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
。 InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
回答by Kymon Lown
Here's a little trick I used, in this "list" is an ArrayList:
这是我使用的一个小技巧，在这个“列表”中是一个 ArrayList：
Dim ValidateList As String
For Each x In list
ValidateList = ValidateList + x + Chr(44)
Next
With Sheets(yoursheet).Range(yourCell).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=ValidateList
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Might seem a bit crude, but I think it works without any problems :)
可能看起来有点粗糙，但我认为它没有任何问题:)
