Excel vba: data validation mandatory and maximum field length

Question

I want to have some data validations for the given range ( D2:D65536 ).

The validation criteria must check the cells within the range must not be empty and the text length should not more than 35.

How to implement this validation using vba?

I have tried:

'    With Range("D2:D65536").Validation
'        .Delete
'        .Add Type:=xlValidateTextLength, _
'            AlertStyle:=xlValidAlertInformation, _
'            Minimum:=2, Maximum:="35"
'        .IgnoreBlank = True
'        .ErrorTitle = "Validation Error"
'        .ErrorMessage = "X mandatory and length should be less than or equal to 35"
'        .ShowError = True
'    End With

Answer 1

I think you are looking for:

Sub Validate()
    With Range("D2:D65536").Validation

        .Delete
        .Add Type:=xlValidateTextLength, _
            AlertStyle:=xlValidAlertInformation, _
            Operator:=xlBetween, _
            Formula1:="2", _
            Formula2:="35"
        .IgnoreBlank = True
        .ErrorTitle = "Validation Error"
        .ErrorMessage = "X mandatory and length should be less than or equal to 35"
        .ShowError = True
    End With
End Sub

The Help on F1 is less confusing than on a search, but any way have a look at the Add method : http://msdn.microsoft.com/en-us/library/aa221688(v=office.11).aspx. The example you used is for whole numbers, but you wish to check text length.

Answer 2

Function checkrange()
Dim r, a As Range

'Set r = Range("D2:D65536")
Set r = Range("a1:a2")
    checkrange = False
    For Each a In r
       If (a = "" Or Len(a) > 35) Then Exit Function
    Next a
    checkrange = True
End Function

Sub a()
MsgBox (checkrange())
End Sub  

Return True if validation succeeds, False otherwise.

HTH!

Edit

You can read an approach done by using Validation here. But I think it is not robust enough for production code. Validating empty cells without VBA is not possible in a consistent way AFAIK.