如果值存在于数据验证列表中，则粘贴到“数据验证"中 [英] Pasting in Data Validation IF the value exists inside the data validation list

问题描述

一个类似的问题已被问过多次，但我有一个略有不同的问题.我使用了一些来自超级用户的代码，这些代码限制用户将值粘贴到数据验证范围内:

A similar question has been asked multiple times, but I have a slightly different ask. I have used some code from superuser that restricts users from pasting values into data validation ranges:

Private Sub Worksheet_Change(ByVal Target As Range)
    'Does the validation range still have validation?
    If HasValidation(Range("DataValidationRange")) Then
        Exit Sub
    Else
        Application.Undo
        MsgBox "Error: You cannot paste data into these cells." & _
        "Please use the drop-down to enter data instead.", vbCritical
    End If
End Sub

Private Function HasValidation(r) As Boolean
    'Returns True if every cell in Range r uses Data Validation
    On Error Resume Next
    x = r.Validation.Type
    If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function

这很好并且可以工作，但是我想知道是否可以再进一步.用户可能希望粘贴到这些字段中的原因是因为他们正在将数据从一个电子表格移动到另一个电子表格.我在那里进行验证，以确保拼写正确(对于其他用途很重要).根据上面的代码，如果值与数据验证列表中的内容匹配，用户是否可以将某些内容粘贴到数据验证字段中并且不拒绝它?似乎雄心勃勃，不确定是否可行.

This is great and it works, but I am wondering if it can be taken one step further. The reason users may want to paste into these fields is because they are moving data from one spreadsheet to the other. I have the validation there to ensure the spelling is correct (important for other uses). Is it possible for a user to paste something into the data validation field and it doesn't deny it, based on the code above, IF the value matches something inside the data validation list? Seems ambitious, not sure if it is possible.

列表存储在另一个标签中，未硬编码到数据验证菜单中

The list is stored in another tab, not hardcoded into the data validation menu

推荐答案

如果 Validation 不是 Nothing ，并且其类型是 xlValidateList (基础值 3 )，则可以使用 Validation.Formula1 获取列表".

If the Validation isn't Nothing and its type is xlValidateList (underlying value 3), then you can use Validation.Formula1 to get the "list".

那是容易的部分.

如果 Formula1 不是以 = 符号开头，则您正在查看的是用逗号分隔的简单值列表.

If Formula1 doesn't start with an = sign, you're looking at a plain comma-separated list of values.

此函数将为您提供一维数组，其中包含指定的 target 的所有有效值，而不管如何定义数据验证列表:

This function gets you a 1-dimensional array with all the valid values of the specified target, regardless of how the data validation list is defined:

Public Function GetValidationValues(ByVal target As Range) As Variant
    Dim dataValidation As Validation
    Set dataValidation = target.Validation

    If dataValidation Is Nothing Then Exit Function
    If dataValidation.Type <> xlValidateList Then Exit Function

    Dim values As Variant
    If Left$(dataValidation.Formula1, 1) <> "=" Then
        'plain comma-separated list of values
        values = Split(dataValidation.Formula1, ",")
    Else
        'validation list is a range address, or a named range
        Dim rngValues As Range
        Set rngValues = Application.Evaluate(dataValidation.Formula1)
        If rngValues.Columns.Count > 1 Then
            values = Application.Transpose(Application.Transpose(rngValues))
        Else
            values = Application.Transpose(rngValues)
        End If
    End If
    GetValidationValues = values
End Function

剩下要做的就是确定您粘贴的值是否在该数组中.

All that's left to do is to determine whether your pasted value is in that array.

这篇关于如果值存在于数据验证列表中，则粘贴到“数据验证"中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！