UDF to create a dropdown list?

**jammie** · 10-08-2010, 06:42 AM

Hi,

I'm doing some work for a client but thi has me stumped. He has about 80 cells with data in (long strings of text) which he wants parsing and manipulating (easy enough), but he wants the results (about 10 from each string) in a drop down box (in the table, not a userform).

So in effect he types =generate(B5:B10) and a listbox/dropdown/something appears with the results of the manipulation.

Anyone got any ideas? I assume its not possible as dropdowns are only for datavalidation, but i figured there is no harm in asking just in case!

**DonkeyOte** · 10-08-2010, 06:57 AM

It might be possible to the extent that entering =GENERATE(B5:B10) in essence invokes a sub routine which in turn introduces the relevant DV lists.

What is not clear is whether =GENERATE(B5:B10) means

a) create one list in the cell in which above was entered where list is based on manipulation of B5:B10 values

or

b) create a list in each of B5:B10 based on the results of some manipulation (not yet specified)

It would help if you could provide a sample and outline the specifics of the manipulation process itself.

**jammie** · 10-08-2010, 07:19 AM

the manipulation is as follows;

there is a string of the format "bike:can:laptop:bottle:car:bathroom" (but different words and much longer). We're splitting the string based upon the seperator and searching to see if a subword appears in the list (if "casino" was the word "casino royale" would return aswell etc.)

I've done all this fine, i've got an array of the values pulled from the text, i just need to display them.

the =GENERATE(B5) or =GENERATE(B5:B10) would only create a single drop down list from the results the macro finds (in these cases, 1 cell and and 6 cells repespectively).

I'm going to speak to my client in the morning and see other options for displaying the data, it may be the case that we can simply print them to a file or a different application without having to do this step.

**DonkeyOte** · 10-08-2010, 07:41 AM

Post back if you need to but the general principle I was getting at would be something like:

Please Login or Register  to view this content.

So in essence let's say B5:B8 contains:

Please Login or Register  to view this content.

With the above code in place we could enter:

Please Login or Register  to view this content.

and the formula would be replaced with a DV list of the various items

the above example is VERY basic for ex. assumes

a) contiguous vertical vector as source
b) basic formula construct
c) ignores issue of duplicate items

so it would need some extensive development - it is nothing other than proof of concept so to speak.

Also worth adding that once in place you could not replace any existing DV list with another =GENERATE( ) formula without first removing the DV list constraints (else invalid entry!)

**jammie** · 10-08-2010, 08:33 AM

Ahh, Validation.add; didn't know that command existed!

Thank you very much! Does everything i needed!

Any ideas where i can get an API for excel/word/etc? Probably over looking many things making it harder for myself!

Thanks again!