vba 确定单元格是否包含数据验证

Determine if cell contains data validation

提问by user2385809

I am writing a VBA code that goes through a range of cells checking if each cell has data validation (drop down menu) and if not assign one to it from a list on another sheet.

我正在编写一个 VBA 代码，它通过一系列单元格检查每个单元格是否具有数据验证（下拉菜单），如果没有从另一张纸上的列表中为其分配一个。

I currently have trouble with the line that checks if the current cell already has data validation. I get error 1004 "no cells were found".

我目前在检查当前单元格是否已经具有数据验证的行中遇到问题。我收到错误 1004“未找到单元格”。

Sub datavalidation()

    Dim nlp As Range
    Dim lrds As Long
    Dim wp As Double
    Dim ddrange As Range

    Sheets("DataSheet").Select

        lrds = ActiveSheet.Range("A1").Offset(ActiveSheet.rows.Count - 1, 0).End(xlUp).Row

        Set nlp = Range("I3:I" & lrds)

        For Each cell In nlp

    'error on following line

            If cell.SpecialCells(xlCellTypeSameValidation).Cells.Count < 1 Then
                wp = cell.Offset(0, -8).Value

                Set ddrange = ddrangefunc(wp)

            End If

        Next

End Sub

Any ideas? Thank you

有任何想法吗？谢谢

回答by AgentRev

I know this question is old, but since it comes up when Googling "excel vba check if cell has validation", I figured I would add my grain of salt.

我知道这个问题很老，但是因为它在谷歌搜索“excel vba 检查单元格是否有验证”时出现，我想我会加一点盐。

If the Rangeobject on which you call SpecialCellsrepresents only a single cell, the entire sheet will be scanned to find matches. If you have a very large amount of data, the methods provided in previous answers may become a bit slow.

如果Range您调用的对象SpecialCells仅代表单个单元格，则将扫描整个工作表以查找匹配项。如果你的数据量非常大，之前答案中提供的方法可能会变得有点慢。

Hence, here is a more efficient way to check if a single cell has validation:

因此，这是检查单个单元格是否具有验证的更有效方法：

Function HasValidation(cell As Range) As Boolean
    Dim t: t = Null

    On Error Resume Next
    t = cell.Validation.Type
    On Error GoTo 0

    HasValidation = Not IsNull(t)
End Function

回答by Tim Williams

Dim cell As Range, v As Long

For Each cell In Selection.Cells
    v = 0
    On Error Resume Next
    v = cell.SpecialCells(xlCellTypeSameValidation).Count
    On Error GoTo 0

    If v = 0 Then
        Debug.Print "No validation"
    Else
        Debug.Print "Has validation"
    End If
Next

回答by Gary's Student

If you only want to test the activecell, then:

如果你只想测试activecell，那么：

Sub dural()
    Dim r As Range
    On Error GoTo noval
    Set r = Cells.SpecialCells(xlCellTypeAllValidation)
    If Intersect(r, ActiveCell) Is Nothing Then GoTo noval
    MsgBox "Active cell has validation."
    Exit Sub
noval:
    MsgBox "Active cell has no validation."
    On Error GoTo 0
End Sub

回答by CRUTER

Also, if you'd like to get the validation Sourceyou can use the following...

此外，如果您想获得验证，Source您可以使用以下...

Dim cell as Range
Dim rng as Range
Set rng = Range("A1:A10") 'enter your range

On Error Resume Next 'will skip over the cells with no validation

For Each cell In rng
    msgbox cell.Validation.Formula1
Next cell

回答by Vityata

About 4 years later, I am looking for cell validation as well. Combining a few from the answers here, this is what I came up with:

大约 4 年后，我也在寻找细胞验证。结合这里的一些答案，这就是我想出的：

Option Explicit

Public Sub ShowValidationInfo()

    Dim rngCell             As Range
    Dim lngValidation       As Long

    For Each rngCell In ActiveSheet.UsedRange

        lngValidation = 0

        On Error Resume Next
        lngValidation = rngCell.SpecialCells(xlCellTypeSameValidation).Count
        On Error GoTo 0

        If lngValidation <> 0 Then
            Debug.Print rngCell.Address
            Debug.Print rngCell.Validation.Formula1
            Debug.Print rngCell.Validation.InCellDropdown
        End If
    Next

End Sub

回答by Ricardo Diaz

Looking for a way to handle this avoiding the error resume next this is the way I implement it:

寻找一种方法来处理这个避免错误恢复接下来这是我实现它的方式：

Option Explicit
' https://stackoverflow.com/questions/18642930/determine-if-cell-contains-data-validation
' Use this if you want to omit doing something to the cell added: http://dailydoseofexcel.com/archives/2007/08/17/two-new-range-functions-union-and-subtract/
Sub ValidationCells()

    Dim theSheet As Worksheet
    Dim lastCell As Range
    Dim validationRange As Range
    Dim validationCell As Range

    Application.EnableEvents = False ' optional

    Set theSheet = ThisWorkbook.Worksheets(1)

    theSheet.Unprotect ' optional

    ' Add a cell with a value and some validation to bypass specialcells error
    Set lastCell = theSheet.Cells(1, theSheet.Cells.Columns.Count)
    With lastCell
        .Value2 = 1
        .Validation.Add xlValidateWholeNumber, xlValidAlertInformation, xlEqual, "1"
    End With

    ' If usedrange is greater than 1 (as we added a single cell previously)
    If theSheet.UsedRange.Rows.Count > 1 Or theSheet.UsedRange.Columns.Count > 1 Then

        Set validationRange = theSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)

        MsgBox validationRange.Address

        For Each validationCell In validationRange
            If validationCell.Address <> lastCell.Address Then
                MsgBox validationCell.Address
            End If
        Next validationCell

    End If

    lastCell.Clear

    Set validationRange = Nothing
    Set lastCell = Nothing

    theSheet.Protect ' optional

    Application.EnableEvents = True ' optional


End Sub

回答by Sandro Wiggers

This works for me

这对我有用

Sub test()
    On Error Resume Next
        If ActiveCell.SpecialCells(xlCellTypeSameValidation).Cells.Count < 1 Then
            MsgBox "validation"
        Else
            MsgBox "no Validation"
        End If
    On Error GoTo 0
End Sub