gyetvaig

VBA Check if a sheet has any data validation

Jan 16th, 2019
252
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. ' ----------------------------------------------------------------
  2. ' Purpose: Function to check if a sheet contains any data validation
  3. ' ----------------------------------------------------------------
  4. Function F_sheetHasDataValidation(sh As Worksheet) As Boolean
  5.  
  6.     Dim dataValRange As Range
  7.  
  8.     'Trying to assign data validation cells to a range object, if there is no data validation cells on
  9.    'the sheet dataValRange remains Nothing
  10.    On Error Resume Next
  11.     Set dataValRange = sh.Cells.SpecialCells(xlCellTypeAllValidation)
  12.     On Error GoTo 0
  13.  
  14.     'If dataValRange is Nothing there is no data validation cell on the sheet
  15.    If dataValRange Is Nothing Then
  16.         F_sheetHasDataValidation = False
  17.     Else
  18.         F_sheetHasDataValidation = True
  19.         Set dataValRange = Nothing
  20.     End If
  21.  
  22. End Function
Advertisement