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.
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...
