Combobox alternatives based on a range

[Pero]

Hi,

I have a simple but still very annoying problem...

I have a list of persons, as in my dummybook. The list can contain any
number of personons. I want to put all of the dates in a combobox as
alternatives, but only one of each and no blank alternatives.

So the combobox should contain unique values and no blank values!

The combobox should also uppdate itself when a new post is added.

How do I get this to work ?

Many thanks!!

//

Per

[jaslake]

Hi Pero

One way is to create a dynamic named range of the dates. Advanced Filter the date range (including the header) to sheet2 then name that range. See attached.

Sorry, I left a piece of code out...I'll repost the workbook.

[Pero]

Hi,

thanks for reply!

It works but is there a way of doing this whithout copying the filter result to a new sheet and then add them to a combobox ? w
It must be possible to write a VBA-code that adds all unique dates to a array and then add the array to the combobox or something ?
Without any visual interaction!

Thanks again!

[jaslake]

Hi Pero

Actually, there is a way to do this

It must be possible to write a VBA-code that adds all unique dates to a array

and I worked with something like that recently. I'll need to dig through my scrap heap and see if I can give you an example.

[jaslake]

Hi Pero

Notice this Requires, Tools > Reference > Microsoft Scripting Runtime, scrrun.dll
Place this code in a general module

Please Login or Register  to view this content.

Place this code in Sheet1

Please Login or Register  to view this content.

Let me know of issues.

[Pero]

Hi, Thanks ! Almost works..

If the list only contains one personi get an error "Type missmatch" on
line:

Please Login or Register  to view this content.

That must be because anArray is difined as a variant and when it only contains one element variant will choose "wrong" datatype for "anArray" and "For Each a in anArray" wont work.

I'm I right ?


EDIT:

Also , the dates in the combobox are displayed in another format then in the actual list. The list contains dates in format YYYY-MM-DD but in the combobox list its : MM-DD-YYYY
I want it to be YYYY-MM-DD in the combobox aswell!

Thanks again !

[jaslake]

Hi Pero

Modify the code as follows

Please Login or Register  to view this content.

Let me know of issues.

[Pero]

It works =) .
Thnaks you very much!