Test for validation list in a cell

[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

 

[Dave Peterson]

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

 

[Bob Phillips]

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