VBA - cycle through all cells with validation

blobbles

New Member
Joined
Apr 6, 2011
Messages
4
Wondering if there is a way to cycle through all cells on a form and check if they have validation. If they have, is it possible to return the validation criteria?

Thanks!
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
LIke this maybe:

Code:
Sub ShowValidation()
    Dim oCell As Range
    Dim sMsg As String
    For Each oCell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)
        With oCell.Validation
            sMsg = oCell.Address & vbNewLine
'            .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
'                 Operator:=xlBetween, Formula1:="1", Formula2:="10"
            sMsg = sMsg & " .type = " & .Type & vbNewLine
            sMsg = sMsg & " .Alertstyle = " & .AlertStyle & vbNewLine
            sMsg = sMsg & " .Operator = " & .Operator & vbNewLine
            sMsg = sMsg & " .Formula1 = " & .Formula1 & vbNewLine
            sMsg = sMsg & " .Formula2 = " & .Formula2 & vbNewLine
            sMsg = sMsg & " .IgnoreBlank = " & .IgnoreBlank & vbNewLine
            sMsg = sMsg & " .InCellDropdown = " & .InCellDropdown & vbNewLine
            sMsg = sMsg & " .InputTitle = " & .InputTitle & vbNewLine
            sMsg = sMsg & " .ErrorTitle = " & .ErrorTitle & vbNewLine
            sMsg = sMsg & " .InputMessage = " & .InputMessage & vbNewLine
            sMsg = sMsg & " .ErrorMessage = " & .ErrorMessage & vbNewLine
            sMsg = sMsg & " .ShowInput = " & .ShowInput & vbNewLine
            sMsg = sMsg & " .ShowError = " & .ShowError & vbNewLine
            MsgBox sMsg
        End With
    Next
End Sub
 
Upvote 0
Sorry, have found the answer myself, posting the code for anyone else to see:

Code:
    Set rngValidation = ws.Range(ws.Range("A1"), ws.Range(LastCellAddress)).SpecialCells(xlCellTypeAllValidation)
    
    'find cells with validation
    If Not rngValidation Is Nothing Then
        For Each rngValidationCell In rngValidation.Cells
            'do stuff
        Next rngValidationCell
    End If
 
Upvote 0
Thanks jk, your solution is very similar to what I found...

Buuuut.... I am getting an error, when there are no validation cells on the sheet. The code breaks with the error "No cells were found."

This seems annoying, a pity there isn't a validation cell count property somewhere (I realise they aren't a collection so won't have a count property) which I can test for 0 cells. Instead it seems I will have to put *gammy* On Error Go To codes around the code to handle when something isn't found. Sorry, it just looks tacky!
 
Upvote 0

Forum statistics

Threads
1,224,557
Messages
6,179,503
Members
452,917
Latest member
MrsMSalt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top