CODEWITHSUNDEEP
excelvbavalidationms-access
abdol-hamid Hosseiny
abdol-hamid Hosseiny

Reputation: 132

can excel vba (function or Sub) return List of Data For "Data Validation >> List >> Source"?

can excel vba (function or Sub) return List of Data For "Data Validation >> List >> Source" ? Data Validation List (Drop Down List / combo Box) data source from my vba code Like:

= MyFunction()
return Like:
Apple,
Tool,Bag,Everything ...

more details: I need to search something with multi condition

Function myFunction(Rng1 As Range)
txtSearch As String
txtSearch = Rng1.Text
' do somting 

' return
' aApple,Tool,Bag,Everything ...
End Function

thanks for advice.

Upvotes: 1

Views: 141

Answers (1)

FaneDuru
FaneDuru

Reputation: 42236

Please, try using the next function:

Function extractValidationList(vCell As Range) As String
    Dim strFormula As String
    
     strFormula = vCell.Validation.Formula1
        
     If left(strFormula, 1) = "=" Then
        Dim inputRange As Range, c As Range
        Set inputRange = Evaluate(strFormula)
        If inputRange.rows.count > inputRange.Columns.count Then
            'extract a 1D array from a range with more rows and one column and Join it
            extractValidationList = Join(Application.Transpose(inputRange.value), ", ")
        ElseIf inputRange.Columns.count > inputRange.rows.count Then
            'extract a 1D array from a range with more columns and one row and Join it
            extractValidationList = Join(Application.Transpose(Application.Transpose(inputRange.value)), ", ")
        End If
    Else
        Dim arrF, listSep As String
        listSep = Application.International(xlListSeparator)
        arrF = Split(strFormula, listSep)
        extractValidationList = Join(arrF, ", ")
    End If
End Function

It can be tested using the next Sub:

Sub testExtractValidationList()
   Debug.Print extractValidationList(ActiveCell) 'previously select the validated cell...
End Sub

Or using it UDF (User Defined Function), writing a formula in a cell:

=extractValidationList(C6)

where, C6 is the list validated cell, where from to extract the list...

Upvotes: 3

Related Questions