Avatar of dlmille
dlmille
Flag for United States of America asked on

Data Validation List directly from UDF Function

Per related question, Please see attached workbook.

Please notice in the spreadsheet, I use this function with an INDEX to get the 1 to many results for a criteria-based list, which is then identified by a data validation cell, and all works correct.  See attached excel file, the cell to focus on is the yellow one which references column B whose formulas are referencing this function.

Of course, having this "parallel" list in column B is a little awkward, and without doing something fancy with a combobox or something, I'm wondering whether a DV List will accept a comma separated list of values direclty, via a UDF function.

Your assistance would be appreciated in modifying the attached code such that the results can go directly to the DV list.   It would be great not to have to have Column B to make this all work.

Dave
Function subStrList(kwd As Range, DVList As Range) As Variant
Dim foundRange As Range, lastRow As Long
Dim myDVList() As String, cnt As Long

    lastRow = DVList.Cells(1, 1).Row + DVList.Rows.Count - 1

    Set foundRange = DVList.Find(what:=kwd, LookIn:=xlValues, lookat:=xlPart)
    If Not foundRange Is Nothing Then
        'subStrList = foundRange.Value
        ReDim Preserve myDVList(cnt) As String
        myDVList(cnt) = foundRange.Value
        cnt = cnt + 1
        Do
            Set foundRange = DVList.Find(what:=kwd, after:=foundRange, LookIn:=xlValues, lookat:=xlPart)
            If Not foundRange Is Nothing Then
                'subStrList = subStrList & "," & foundRange.Value
                ReDim Preserve myDVList(cnt) As String
                myDVList(cnt) = foundRange.Value
                cnt = cnt + 1
            End If
        Loop While Not foundRange Is Nothing And foundRange.Row < lastRow
    End If
    
    subStrList = myDVList
End Function

Open in new window

DV-ListFun-r4.xls
Microsoft Excel

Avatar of undefined
Last Comment
dlmille

8/22/2022 - Mon
Rory Archibald

I don't believe you can do it directly - you'd need an event routine that alters the validation list using a comma-separated list returned by the function. It might be possible to do it in-cell using windows timers but I suspect the function would only get called when you clicked the dropdown and Excel would probably crash if you tried to change the list then.
dlmille

ASKER
Rorya - I've tried the function in a named range, in the DV list, etc., to no avail, so from your perspective is creating a parallel list beside the original the best approach?

Dave
ASKER CERTIFIED SOLUTION
Rory Archibald

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
byundt

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
dlmille

ASKER
If I can't directly address it in the DV formula (either as a function, or addressing the adjacent almost-duplicate column), then I probably would load a comboBox and drop it on top, lol.

Dave
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
dlmille

ASKER
Thanks for the advice.