以编程方式创建验证列表 [英] Programmatically creating validation list

问题描述

我有一个数据数组，这些数据是从外部源输入到VBA代码中的.我希望能够在该工作簿的一张纸上的一个单元格的下拉框中分配该数据以用作验证.但是，我不想将这些数据复制到工作表中，然后使用一个命名范围-可能有很多数据，而且效率不高！

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!

我确定必须有一种方法-但我还没有找到一种方法.有什么想法吗?

I'm sure there must be a way - but I haven't found one yet. Any ideas?

推荐答案

1. 将数据放在一些文本文件中，并用逗号分隔，例如(a，b，c).

1. Place the data in some text file delimiting it with comma eg(a,b,c).

使用VBA将数据读取到字符串变量(例如ValidationList)中.

Read that data using VBA into a string variable eg ValidationList.

使用类似这样的东西

具有Range("A1").Validation
.添加类型:= xlValidateList，AlertStyle:= xlValidAlertStop，运算符:= _
xlBetween，公式1:= ValidationList
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle ="
.ErrorTitle ="
.InputMessage ="
.ErrorMessage ="
.ShowInput = True
.ShowError = True
结尾为

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

这篇关于以编程方式创建验证列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！