Determine if cell is using data validation

[Roderick_E]

Say I'm looping through rows 1-50, columns 1-10 and I want to determine if a cell is using data validation. How can I determine that?
I tried .validation but that must not be it. Thanks

NEVERMIND -- look like validation.type = 3

 

[Dryver14]

.validation what, looking at other code it seems to be ok.

I'm sure you are aware of this but i will throw it in for anyone reading the thread. there is data validation option on the find and selct on the home tab to show any data validation on the sheet

 

[Roderick_E]

.validation what, looking at other code it seems to be ok.

I'm sure you are aware of this but i will throw it in for anyone reading the thread. there is data validation option on the find and selct on the home tab to show any data validation on the sheet

Thanks I need VBA since I'm resetting some cells. type=3 isn't working perfectly yet.

 

[Dryver14]

what is type 3 what are you looking for?

 

[Fluff]

How about specialcells

Cells.SpecialCells(xlCellTypeAllValidation).Select

 

[MARK858]

Using a function...

Sub testValid()
    Dim myCell As Range

    For Each myCell In Range("A1:J50")
        If HasVal(myCell) Then Debug.Print myCell.Address
    Next
End Sub

Function HasVal(cTest As Range) As Boolean
    Dim vTest
    vTest = Null

    On Error Resume Next
    vTest = cTest.Validation.Type
    On Error GoTo 0

    HasVal = Not IsNull(vTest)
End Function

or following on from Fluff's suggestion...

Sub testValid2()
    Dim myCell As Range, myVal As Long

    For Each myCell In Range("A1:J50")
        myVal = 0

        On Error Resume Next
        myVal = myCell.SpecialCells(xlCellTypeSameValidation).Count
        On Error GoTo 0

        If myVal <> 0 Then Debug.Print myCell.Address

    Next

End Sub

 

[Roderick_E]

How about specialcells

Cells.SpecialCells(xlCellTypeAllValidation).Select

Doesn't this error if a cell doesn't have validation?

 

[Roderick_E]

Using a function...

Sub testValid()
    Dim myCell As Range

    For Each myCell In Range("A1:J50")
        If HasVal(myCell) Then Debug.Print myCell.Address
    Next
End Sub

Function HasVal(cTest As Range) As Boolean
    Dim vTest
    vTest = Null

    On Error Resume Next
    vTest = cTest.Validation.Type
    On Error GoTo 0

    HasVal = Not IsNull(vTest)
End Function

or following on from Fluff's suggestion...

Sub testValid2()
    Dim myCell As Range, myVal As Long

    For Each myCell In Range("A1:J50")
        myVal = 0

        On Error Resume Next
        myVal = myCell.SpecialCells(xlCellTypeSameValidation).Count
        On Error GoTo 0

        If myVal <> 0 Then Debug.Print myCell.Address

    Next

End Sub

Thanks. I'll give these a try. I thought type 3 was always validation types but apparently not.

 

[Fluff]

Doesn't this error if a cell doesn't have validation?

Yes, but you'd wrap it in an error handler, in the same way that Mark858 has done in post#6