Excel VBA：更改后验证单元格的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10179150/
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
Excel VBA: Validating a cell's value after changing it
提问by rcell
I have a function that updates a range of cells to a certain value after the user changes one cell to this value. However the cells in this range do not share the same validation range so I would like to do separate validation for each cell updated.
我有一个函数，可以在用户将一个单元格更改为此值后将一系列单元格更新为某个值。但是，此范围内的单元格不共享相同的验证范围，因此我想对更新的每个单元格进行单独验证。
Here's what I have so far. For each cell, the data validation is set to its own validation range with acceptable values. For some reason the validation holds true even though the data is not in the validation range. I'm not too sure how the validation.value property works..
这是我到目前为止所拥有的。对于每个单元格，数据验证设置为具有可接受值的自己的验证范围。出于某种原因，即使数据不在验证范围内，验证仍然成立。我不太确定validation.value 属性是如何工作的..
For index = 1 to UBound(someArray)
wksSomeSheet.Cells(index, column).Value = requiredValue
If Not wksSomeSheet.Cells(index, column).Validation.Value Then
MsgBox "A value is not supported for one the cells."
Exit For
End If
Next
Or should I implement it so that I use the Range("validationRange").find(requiredValue)instead?
或者我应该实现它以便我使用它Range("validationRange").find(requiredValue)？
采纳答案by rcell
After having looked at Siddharth's blog, I made some minimal changes to his code to adapt it to my needs:
在看过 Siddharth 的博客后，我对他的代码做了一些最小的改动以适应我的需要：
Dim currentValidation As Excel.Validation
wksSomeSheet.Cells(index, Target.Column).Value = requiredValue
Set currentValidation = wksSomeSheet.Cells(index, Target.Column).Validation
If currentValidation.Type = xlValidateList Then
Dim validationFound As Boolean, MyArray As Variant
validationFound = False
MyArray = Application.WorksheetFunction.Transpose(Range(Mid(currentValidation.Formula1, 1)))
For i = 1 To UBound(MyArray)
If requiredValue = MyArray(i, 1) Then
validationFound = True
Exit For
End If
Next
If Not validationFound Then
MsgBox wksSomeSheet.Cells(index, wksSomeSheet.Range("myRange").Column).Value & " does not have a valid value."
Exit For
End If
End If
