Avatar of spar-kle
Flag 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?
Microsoft Excel

Avatar of undefined
Last Comment

8/22/2022 - Mon

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.

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



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

Your help has saved me hundreds of hours of internet surfing.

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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

Thanks for your help
Really appreciated