Data validation - testing for size of list

[joey peanuts]

Excel 2007

I want a way to test the number of items in a DV list. The list size changes as previous selections are made in other data entry columns.

If there is just 1 item in a particular list, I want to programatically select that item so the user doesn't have to.

Thank you

 

[rob_sheeds]

You should put an example or more detailed explanation....sounds like some VBA is needed though.

 

[joey peanuts]

I am looking for a function that would return the number of items in a DV list - something like this:


Dim numLISTITEMS as Integer

numLISTITEMS = Range ("A1").validation.count

 

[shg]

Maybe like this (minimally tested)

Function ValListLen(r As Range) As Long
    Dim s As String
 
    On Error GoTo Oops
    With r(1)
        If .Validation.Type = xlValidateList Then
            s = .Validation.Formula1
            If InStr(s, ",") Then
                ValListLen = UBound(Split(s, ",")) + 1
            Else
                ValListLen = Range(s).Cells.Count
            End If
        End If
    End With
Oops:
End Function

 

[joey peanuts]

Maybe like this (minimally tested)

Function ValListLen(r As Range) As Long
    Dim s As String
 
    On Error GoTo Oops
    With r(1)
        If .Validation.Type = xlValidateList Then
            s = .Validation.Formula1
            If InStr(s, ",") Then
                ValListLen = UBound(Split(s, ",")) + 1
            Else
                ValListLen = Range(s).Cells.Count
            End If
        End If
    End With
Oops:
End Function

Wow - very creative. If all really want to know is if there is 1 item in the list, could I just declare the ValListLen function as a boolean? Then, use the InStr to look for ",". If comma is found, set ValListLen to false. If comma not found, then set to true (meaning just 1 item).

Thank you

 

[shg]

I forgot about the case where there is only a single item in a literal list:

Function ValListLen(r As Range) As Long
    Dim s As String
 
    On Error GoTo Oops
    With r(1)
        If .Validation.Type = xlValidateList Then
            s = .Validation.Formula1
            If Left(s, 1) = "=" Then
                ValListLen = Range(s).Cells.Count
            Else
                ValListLen = UBound(Split(s, ",")) + 1
            End If
        End If
    End With
 
Oops:
    If Err.Number Then Err.Clear
End Function

For example,

If ValListLen(Range("A1")) = 1 Then

 

[joey peanuts]

It works perfect. Thank you very much.