vba - Excel VBA : Validating a cell's value after changing it

Question

我有一个函数可以在用户将一个单元格更改为该值后将一系列单元格更新为某个值。但是，此范围内的单元格不共享相同的验证范围，因此我想对每个更新的单元格进行单独验证。

这是我到目前为止所拥有的。对于每个单元格，数据验证设置为其自己的验证范围和可接受的值。由于某种原因，即使数据不在验证范围内，验证仍然成立。我不太确定 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

或者我应该实现它以便我使用 Range("validationRange").find(requiredValue)反而？

Answer 1

在查看了 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