在 VBA 中使用动态列表验证
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16199027/
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
Using a Dynamic List Validation in VBA
提问by Pietro
I'm new to programing in VBA and I'm trying to validate data with a list in an Excel worksheet. The problem is the list varies in size every time I select a different criteria from a drop-down list.
我是 VBA 编程的新手，我正在尝试使用 Excel 工作表中的列表验证数据。问题是每次我从下拉列表中选择不同的标准时，列表的大小都会有所不同。
For example: when I select China, the list turns into 10 different sellers. Range A1 to A10, but when I select Japan I only have 5 sellers, from A1 to A5.
例如：当我选择 CN 时，列表变成了 10 个不同的卖家。范围从 A1 到 A10，但是当我选择日本时，我只有 5 个卖家，从 A1 到 A5。
So I need a new range in the the Formula1part each time.
所以我每次都需要在Formula1部分有一个新的范围。
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$Z:$Z"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
What would be the best way to do this?
什么是最好的方法来做到这一点？
I know if I leave a fixed range it can work but it doesn′t look OK, because it leaves a lot of empty spaces and it doesn't look neat.
我知道如果我离开一个固定的范围它可以工作，但它看起来不太好，因为它留下了很多空白，而且看起来不整洁。
I hope it′s understandable.
我希望这是可以理解的。
采纳答案by Ateszki
You can get the las non empty cell of the column with something like this
你可以用这样的东西得到列的 las 非空单元格
Worksheets("Sheet1").Range("A1").End(xlDown)
Then you just build your Formula1 properties from A1 to the result.
然后，您只需构建从 A1 到结果的 Formula1 属性。
Dim strFormula1 as string
strFormula1 = "=$A:" & Worksheets("Sheet1").Range("A1").End(xlDown).Address()
Hope it helps, have not tested might have errors
希望有帮助，没测试过可能有错误
回答by Bruno Leite
Use a Named Range with formula
将命名范围与公式一起使用
To create a Name go to Formula / Name Manager / New
要创建名称，请转到“公式”/“名称管理器”/“新建”
Choise a name, for example DataValidation, in RefersTo use
选择一个名称，例如 DataValidation，在 RefersTo 中使用
=OFFSET(Sheet1!$Z,0,0,COUNTA(Sheet1!$Z:$Z),1)
Now, you have a dynamic interval , and can use on your validation.
现在，您有一个动态的 interval ，可以用于您的验证。
回答by David Zemens
The other two answers are simpler, but will not work if you ever have non-continous non-blank cells that need to be in the validation list. This method should overcome that :)
其他两个答案更简单，但如果您有需要在验证列表中的非连续非空白单元格，则将不起作用。这种方法应该克服:)
You could use a custom function in VBA to return the filtered string address. This will return the filtered address, or it will return the original address IFthe filtered address is not a valid range.
您可以在 VBA 中使用自定义函数来返回过滤后的字符串地址。这将返回过滤后的地址，或者如果过滤后的地址不是有效范围，它将返回原始地址。
NOTEThis will likely fail if the returned address exceeds the 255-character limit.
注意如果返回的地址超过 255 个字符的限制，这可能会失败。
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=GetAddress(Range("$Z:$Z"))
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Place the function in a normal code module.
将该函数放在一个普通的代码模块中。
Function GetAddress(myRange As Range) As String
Dim cl As Range
Dim c As Long: c = 1
Dim tmpAddress As String
For Each cl In myRange
If cl.Value <> vbNullString Then
'Create a string value of cell address matching criteria'
If tmpAddress = vbNullString Then
tmpAddress = myRange.Cells(c).Address
Else:
tmpAddress = tmpAddress & "," & myRange.Cells(c).Address
End If
End If
c = c + 1
Next
If Not Range(tmpAddress) Is Nothing Then
GetAddress = "=" & tmpAddress
Else:
MsgBox "There are no non-empty cells in this range.", vbInformation
GetAddress = "=" & myRange.Address
End If
End Function
