Determine if cell contains data validation

by

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.

If the Range object on which you call SpecialCells represents 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.

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

More Related Contents:

  1. VBA – How to grab the cell below if a cell has text?
  2. Getting ScriptControl to work with Excel 2010 x64
  3. Pop up the Excel Statusbar?
  4. Get list of Excel files in a folder using VBA [duplicate]
  5. When one should use Set [e.g for SpecialCells return value]?
  6. Should I turn .CutCopyMode back on before exiting my sub procedure?
  7. Sheet protection: UserInterFaceOnly gone
  8. VBScript – How to make program wait until process has finished?
  9. How to highlight a cell using the hex color value within the cell?
  10. Removing special characters VBA Excel
  11. Open an Excel file from SharePoint site
  12. VBA recognizing workbook by partial name
  13. Excel VBA wildcard search
  14. How do I loop an excel 2010 table by using his name & column reference?
  15. Excel VBA Project has generated multiple Workbook objects
  16. Copy an entire worksheet to a new worksheet in Excel 2010
  17. How do I get values from a hidden worksheet using VBA [closed]
  18. Excel VBA, code to count the # of filled cells, then find highest and 2nd highest and subtract the corresponding column #’s [closed]
  19. Programmatic Access To Visual Basic Project Is Not Trusted
  20. Can I make this macro more efficient or faster?
  21. Conditional Formatting using Excel VBA code
  22. Controlling IE11 “Do you want to Open/Save” dialogue window buttons in VBA
  23. How to get the background color from a Conditional Formatting in Excel using VBA
  24. VBA Excel Populate ListBox with multiple columns
  25. VBA Integer Overflow at 70,000
  26. VBA Macro to compare all cells of two Excel files
  27. Cannot VBA write data to cells in Excel 2007/2010 within a function
  28. Convert RTF (Rich Text Format) code into plain text in Excel
  29. lastrow and excel table.
  30. How to have vba execute every 10 minutes?

Leave a Comment