Test for validation list in a cell

  • Thread starter Thread starter Sandy
  • Start date Start date
S

Sandy

I have tried to post this for ages now in programming. But it does not show
up - apologies if this is the wrong group.

How do I check for a validation InCellDropDown list in a range

Roughly:-

If Range("C30:K30,C36:K36") 'has validation' Then

*****Code******
Else

*****Code******
End If

Thanks
Sandy
 
This looks at each cell to see if it has .incelldropdown.

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range
Dim AtLeastOneHasInCellDropDown As Boolean

With Worksheets("sheet1")
Set myRng = .Range("C30:K30,C36:K36")
End With

AtLeastOneHasInCellDropDown = False
For Each myCell In myRng.Cells
On Error Resume Next
AtLeastOneHasInCellDropDown = myCell.Validation.InCellDropdown
On Error GoTo 0
If AtLeastOneHasInCellDropDown = True Then
Exit For
End If
Next myCell

MsgBox AtLeastOneHasInCellDropDown

End Sub

If you wanted to check all the cells, you could count all that have
..incelldropdowns and compare it to the number of cells in that range:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range
Dim InCellDropDownCtr As Long
Dim ThisCellHasInCellDropDown As Boolean

With Worksheets("sheet1")
Set myRng = .Range("C30:K30,C36:K36")
End With

InCellDropDownCtr = 0
For Each myCell In myRng.Cells
ThisCellHasInCellDropDown = False
On Error Resume Next
ThisCellHasInCellDropDown = myCell.Validation.InCellDropdown
On Error GoTo 0
If ThisCellHasInCellDropDown = True Then
InCellDropDownCtr = InCellDropDownCtr + 1
End If
Next myCell

MsgBox InCellDropDownCtr & vbLf & myRng.Cells.Count

End Sub
 
If Not Range("C30:K30,C36:K36").SpecialCells(xlCellTypeAllValidation) Is
Nothing Then

If
Range("C30:K30,C36:K36").SpecialCells(xlCellTypeAllValidation).Cells.Count =
_
Range("C30:K30,C36:K36").Cells.Count Then
 
Back
Top