Excel VBA macro: how do I find cells with list validation

0
votes

I'd like a macro to clear all cells in a given range on a worksheet UNLESS it has (specifically) LIST validation. (In that case I want to make it = 'Select'.)

So... I need the macro to:

1)check all cells within a range on the sheet 2)if the cell does NOT have (specifically) LIST validation it will make the cell = "" 3)if the cell DOES have (specifically) LIST validation it will make the cell = 'Select'

Something like this:

Dim x as variant

with thisworkbook.sheets("audits") 
For each x in .range("A6:AZ200")
    if x.validationtype = "list" then
    x.value = "Select"
    else
    x.value = ""
    end if
next x
end with

Thanks!

2
excelvba
What's the question Joe? Are you having problems getting some code to work (in which case, post the code and tell us what's happening) or try some Google-Fu as I'm sure someone somewhere has built something similar. If you then need help getting that to work, let us know. – CLR
Hi, @CLR - I don't post a question until after google-fu and, specifically, stackoverflow-fu. I'll update my question but not sure how to make it more clear. – Joe Patrick

2 Answers

3
votes

You can probably use the SpecialCells property of a range object to return ONLY the cells with validation, and then do another check to ensure that the validation type is List.

Dim rng As Range
Dim vRng As Range
Dim cl As Range

Set rng = thisworkbook.sheets("audits").Range("A6:AZ200") 'Modify as needed

'Get a range of ONLY the validation cells
Set vRng = rng.SpecialCells(xlCellTypeAllValidation)


For Each cl In rng
    'If the cell has NO VALIDATION:
    If Intersect(cl, vRng) Is Nothing Then
        cl.ClearContents
    ElseIf cl.Validation.Type = 3 Then  'xlValidateList
        cl.Value = "Select"
    End If
Next

Note: 3 is the xlDVType constant for "List" validation. You could alternatively use the constant expression: xlValidateList

The above should handle mixed validation type, and will do nothing with any other sort of validation. If it's safe to assume that ONLY list validation is used, then try condensing it as:

Set vRng = rng.SpecialCells(xlCellTypeAllValidation)
vRng.Value = "Select"

For Each cl In rng
    'If the cell has NO VALIDATION:
    If Intersect(cl, vRng) Is Nothing Then
        cl.ClearContents
    End If
Next
1
votes

This would be one way to do it. In order to keep the error handling away from your main routine I've put the validation checker into a standalone function:

Sub clear_validation()

Dim x As Range

With ThisWorkbook.Sheets("audits")
    For Each x In .Range("A6:AZ200")
        If validationtype(x) = 3 Then
            x.Value = "Select"
        Else
            x.Value = ""
        End If
    Next x
End With

End Sub

Function validationtype(cl As Range)
    Dim t As Integer
    t = 0
    On Error Resume Next
        t = cl.Validation.Type
    On Error GoTo 0
    validationtype = t
End Function

It's flicker-y, so you might want to temporarily turn off screen updating, and perhaps calculations while it's running, but I think this does what you're after.