Link to home
Avatar of spar-kle
spar-kleFlag for United Kingdom of Great Britain and Northern Ireland

asked on

How can I fill a combobox in a form with a list of unique months that correspond to a range of cells that contain dates?

Column A contains dates.
The dates are not consecutive and not necessarily in order.
How can I fill a form combobox with a unique list of months in calendar order, that corresponds to the dates in column A?
Avatar of Steve
Flag of United Kingdom of Great Britain and Northern Ireland image

Use a dictionary object to "read" column A.
This will fill the dictionary with the unique months.
Then use the Dictionary.Keys to fill the combobox.

To get started try reading this excelent article by MathewsPatrick Dictionaries

If you need further assistance we can do the actual code.
but you should be able to "work it out" from the article.
Avatar of spar-kle


Thanks for your reply.
I am already using a dictionary object in my code that I found on the internet.
I have tried various types of similar code, but I can't seem to list the months instead of dates.
I didn't submit my code in case there was something fundamentally wrong.
Here's the code:

Private Sub UserForm_Initialize()

Dim a, v, x, ws As Worksheet
Set ws = Sheets("Data")
a = ws.Range("A4", ws.[A65536].End(xlUp)).Value
With CreateObject("scripting.dictionary")
For Each v In a
If Not IsEmpty(v) And Not .exists(v) Then
.Add v, Nothing
End If
x = .keys
End With
With Me.ComboBox1
.List = x
.ListIndex = 0 ' select the first item
End With

End Sub

Open in new window

Avatar of Rgonzo1971


you have to get the month information out of the date first

Set d = CreateObject("scripting.dictionary")
With d
For Each v In a
    If Not IsEmpty(v) Then
        str_v_month = Format(v, "mmm")
      '  int_v_month = Month(v)   'for month in number
        If Not .Exists(str_v_month) Then
            .Add str_v_month, Null
        End If
    End If

Open in new window

Sorry but can you spell it out by integrating with my code please?
Avatar of Rgonzo1971

Blurred text
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
Thanks for your help
Really appreciated