Choose only the year in the combobox

[Rolsu]

Hi all!

I'd like to do a timetable so I can easily manage my projects and I need help to complete my macro.
I have a combobox where I'd like to select the year. I also have many dates (one can be repetative) in a different sheet in column "A" (with header) that I'd like to use as the source of the CB's list. I know how to refer that list so I can choose the values in the combobox.
My queation is how can I select only the year in the combobox using the list mentioned above? Is it possible to do this only with macro? And it would be nice if one year is included only ones.
Thank you.

 

[Bosquedeguate]

Hi all!

I'd like to do a timetable so I can easily manage my projects and I need help to complete my macro.
I have a combobox where I'd like to select the year. I also have many dates (one can be repetative) in a different sheet in column "A" (with header) that I'd like to use as the source of the CB's list. I know how to refer that list so I can choose the values in the combobox.
My queation is how can I select only the year in the combobox using the list mentioned above? Is it possible to do this only with macro? And it would be nice if one year is included only ones.
Thank you.

If I understand correctly, you have a list of dates in a worksheet that is populating a ComboBox. You want only the year displayed in the ComboBox, correct? with/without duplicate years?

 

[Rolsu]

If I understand correctly, you have a list of dates in a worksheet that is populating a ComboBox. You want only the year displayed in the ComboBox, correct? with/without duplicate years?

Exactly!

 

[Bosquedeguate]

Exactly!

what version of Excel are you using?

 

[Rolsu]

what version of Excel are you using?

Office 2021.

 

[Fluff]

Office 2021.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Rolsu]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thank for the advise! It is done.

 

[Rolsu]

Meanwhile I read a little in the topic and found that this issue can be solved if I create a dictionary.
I found some hints online but I can't make it work.
My code is:

Private Sub ComboBox2_DropButtonClick()

    Dim dict As Object, item As Variant
    Dim cel, rng As Range
    Dim lr As Long
    
        lr = Cells(Rows.Count, "A").End(xlUp).Row
        
        Set dict = CreateObject("scripting.dictionary")
        Set rng = Worksheets("Time").Range("A1:A" & lr)

                rng.Sort Key1:=rng.Cells(1), Order1:=xlAscending, Header:=xlYes, _
                OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal     ' sort the range

                    For Each cel In rng
                        If Not dict.exists(Format(cel, "yyyy")) Then
                        dict.Add Format(cel, "yyyy"), Format(cel, "yyyy")
                    End If
                    Next

                    For Each item In dict.items
                        ComboBox2.AddItem
                        
                    Next

        Set dict = Nothing
        Set rng = Nothing

End Sub

When I open the drop down list it shows nothing.
Can someone help me with this?
Thank you.

 

[Rolsu]

Meanwhile I read a little in the topic and found that this issue can be solved if I create a dictionary.
I found some hints online but I can't make it work.
My code is:

Private Sub ComboBox2_DropButtonClick()

    Dim dict As Object, item As Variant
    Dim cel, rng As Range
    Dim lr As Long
   
        lr = Cells(Rows.Count, "A").End(xlUp).Row
       
        Set dict = CreateObject("scripting.dictionary")
        Set rng = Worksheets("Time").Range("A1:A" & lr)

                rng.Sort Key1:=rng.Cells(1), Order1:=xlAscending, Header:=xlYes, _
                OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal     ' sort the range

                    For Each cel In rng
                        If Not dict.exists(Format(cel, "yyyy")) Then
                        dict.Add Format(cel, "yyyy"), Format(cel, "yyyy")
                    End If
                    Next

                    For Each item In dict.items
                        ComboBox2.AddItem
                       
                    Next

        Set dict = Nothing
        Set rng = Nothing

End Sub

When I open the drop down list it shows nothing.
Can someone help me with this?
Thank you.

There was a missing "item" element after the Additem, but after added I still not get the desired data...

 

[dmt32]

Hi,
untested but see if this helps you

Place in STANDARD module

Function GetYears(Optional ByVal sh As Object, Optional ByVal ListColumn As Long = 1) As Variant
    Dim rng         As Range, cell       As Range
    Dim Years()     As Variant, YearDate As Variant
    Dim i           As Long
   
    If sh Is Nothing Then Set sh = ActiveSheet
   
    Set rng = sh.UsedRange.Columns(ListColumn)
   
    ReDim Years(1 To rng.Cells.Count)
    For Each cell In rng.Cells
        YearDate = cell.Value
        If IsDate(YearDate) Then
            If IsError(Application.Match(Year(YearDate), Years, 0)) Then
                i = i + 1: Years(i) = Year(YearDate)
            End If
        End If
    Next cell
    i = IIf(i = 0, 1, i)
    ReDim Preserve Years(1 To i)
   
    GetYears = Years
End Function

Function has two optional arguments where you can specify
- worksheet object (default Activesheet)
- column number (default column 1)

to allow use in other parts of your project if needed

to call

Private Sub UserForm_Initialize()
  Me.ComboBox1.List = GetYears(Worksheets("Time"),1)
End Sub

I included the arguments for illustration purposes only - change as required.

Dave