Data Validation Inspector for Excel VBA

Overview

Excel VBA Data Validation

Data validation rules are easy to set up but hard to audit across a large workbook. These two macros scan every sheet and produce clickable reports: one listing all validation rules, and another listing only cells that currently violate their validation constraints.

List_Validations documents every data validation rule in the workbook, while List_Invalid_Validations specifically flags cells where the current value does not satisfy the validation criteria, helping you catch data entry errors that slipped through.

How It Works

List Validations: Iterates through all cells with validation (xlCellTypeAllValidation) across every sheet, recording the sheet name, cell address, and the validation formula
List Invalid Validations: Same scan, but filters for cells where Validation.Value returns False, meaning the cell content violates its own rule
Hyperlinked Navigation: Both macros create a new worksheet with clickable hyperlinks for instant navigation to each flagged cell

The VBA Code

Public Sub List_Validations()
    Dim Dic As Object: Set Dic = CreateObject("Scripting.Dictionary")
    Dim Rng As Range, Wks As Worksheet
    Dim Idx_Lst As Long: Idx_Lst = 1
    Dim New_Wks As Worksheet: Set New_Wks = Worksheets.Add
    
    On Error Resume Next
    For Each Wks In ActiveWorkbook.Sheets
        For Each Rng In Wks.Cells.SpecialCells(xlCellTypeAllValidation)
            If Rng.Validation.Formula1 <> "" Then
                Idx_Lst = Idx_Lst + 1
                New_Wks.Cells(Idx_Lst, 1).Value = Wks.Name
                New_Wks.Cells(Idx_Lst, 2).Value = Rng.Address(False, False)
                New_Wks.Hyperlinks.Add New_Wks.Cells(Idx_Lst, 3), "", _
                    "'" & Wks.Name & "'!" & Rng.Address(False, False)
                New_Wks.Cells(Idx_Lst, 4).Value = "'" & Rng.Validation.Formula1
            End If
        Next Rng
    Next Wks
    On Error GoTo 0
    
    New_Wks.Range("A1").Value = "Worksheet"
    New_Wks.Range("B1").Value = "Cell Address"
    New_Wks.Range("C1").Value = "Hyperlinks"
    New_Wks.Range("D1").Value = "Data Validation"
    New_Wks.Range("A:D").EntireColumn.AutoFit
End Sub

Public Sub List_Invalid_Validations()
    Dim Dic As Object: Set Dic = CreateObject("Scripting.Dictionary")
    Dim Rng As Range, Wks As Worksheet
    Dim Idx_Lst As Long: Idx_Lst = 1
    Dim New_Wks As Worksheet: Set New_Wks = Worksheets.Add
    
    On Error Resume Next
    For Each Wks In ActiveWorkbook.Sheets
        For Each Rng In Wks.Cells.SpecialCells(xlCellTypeAllValidation)
            If (Not Rng.Validation Is Nothing) And (Not Rng.Validation.Value) Then
                Idx_Lst = Idx_Lst + 1
                New_Wks.Cells(Idx_Lst, 1).Value = Wks.Name
                New_Wks.Cells(Idx_Lst, 2).Value = Rng.Address(False, False)
                New_Wks.Hyperlinks.Add New_Wks.Cells(Idx_Lst, 3), "", _
                    "'" & Wks.Name & "'!" & Rng.Address(False, False)
                New_Wks.Cells(Idx_Lst, 4).Value = "'" & Rng.Validation.Formula1
            End If
        Next Rng
    Next Wks
    On Error GoTo 0
    
    New_Wks.Range("A1").Value = "Worksheet"
    New_Wks.Range("B1").Value = "Cell Address"
    New_Wks.Range("C1").Value = "Hyperlinks"
    New_Wks.Range("D1").Value = "Data Validation"
    New_Wks.Range("A:D").EntireColumn.AutoFit
End Sub

Final Thoughts

Validation rules often become stale as workbooks evolve. Running these inspectors periodically ensures your data constraints are documented and that no cells are silently breaking their own rules.