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.

 

[Akuini]

@Rolsu
I amended your code in post #8:

Private Sub ComboBox2_DropButtonClick()

    Dim dict As Object, item As Variant
    Dim cel, rng As Range
    Dim lr As Long
    Dim va
        lr = Worksheets("Time").Cells(Rows.Count, "A").End(xlUp).Row
        Set rng = Worksheets("Time").Range("A2:A" & lr)  'data start at row 2
        Set dict = CreateObject("scripting.dictionary")

                rng.Sort Key1:=rng.Cells(1), Order1:=xlAscending, Header:=xlNo, _
                OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal     ' sort the range
                va = rng.Value
                    For Each cel In va
                        dict(Format(cel, "yyyy")) = Empty
                    Next
                combobox2.List = dict.keys
End Sub

 

[Rolsu]

@Rolsu
I amended your code in post #8:

Private Sub ComboBox2_DropButtonClick()

    Dim dict As Object, item As Variant
    Dim cel, rng As Range
    Dim lr As Long
    Dim va
        lr = Worksheets("Time").Cells(Rows.Count, "A").End(xlUp).Row
        Set rng = Worksheets("Time").Range("A2:A" & lr)  'data start at row 2
        Set dict = CreateObject("scripting.dictionary")

                rng.Sort Key1:=rng.Cells(1), Order1:=xlAscending, Header:=xlNo, _
                OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal     ' sort the range
                va = rng.Value
                    For Each cel In va
                        dict(Format(cel, "yyyy")) = Empty
                    Next
                combobox2.List = dict.keys
End Sub

Hi Akuini!
It works!
One small thing I'd like to ask about this. This code works only when I change the date format in my reference list to dd/mm/yyyy. Is any chance to make this work with the format dd.mm.yyyy? If not than it's ok too. I ask this because the general format in my region in the last one.
Thank you.

 

[Akuini]

This code works only when I change the date format in my reference list to dd/mm/yyyy. Is any chance to make this work with the format dd.mm.yyyy?

I don't understand why it won't work with format dd.mm.yyyy.
What actually happened when you run the code with data format dd.mm.yyyy?
Could you post a sample data?

 

[Rolsu]

I don't understand why it won't work with format dd.mm.yyyy.
What actually happened when you run the code with data format dd.mm.yyyy?
Could you post a sample data?

If I use the original format (24.08.2023) you can see the result in the picture. The sorting function works fine, because one date appears only once but the whole date, not just the year.

 

[Akuini]

If I use the original format (24.08.2023) you can see the result in the picture. The sorting function works fine, because one date appears only once but the whole date, not just the year.

Maybe the date in the sheet isn't really a date but text.
Test it like this:

Book1
	A	B
1	01.02.2020	2020
2	01.02.2020	#VALUE!
3
Sheet4

Cell Formulas
Range	Formula
B1:B2	B1	=YEAR(A1)

I entered data in A1 as date but in A2 as a text, so Year function returns error on A2.
Try YEAR function on your data, see what the result is.

 

[Rolsu]

Maybe the date in the sheet isn't really a date but text.
Test it like this:

Book1
	A	B
1	01.02.2020	2020
2	01.02.2020	#VALUE!
3
Sheet4

Cell Formulas
Range	Formula
B1:B2	B1	=YEAR(A1)

I entered data in A1 as date but in A2 as a text, so Year function returns error on A2.
Try YEAR function on your data, see what the result is.

The format is official date, so the problem was not that I think.
Meanwhile I figured it out. The data source comes from a userform textbox, where I can enter a date, or accept the auto one (that tbox refers to a cell whit the actual date). I formated the tbox value to "dd/mm/yyyy". The result is almost the same with a small difference. After clicking the save button excel stores the date in this format: 24. 08. 2023, instead of this: 24.08.2023. There's a space after the dot in the new format. And now your code works properly.
Thank you!

 

[Akuini]

You're welcome, glad you figured it out.