Vba combobox- date format

[RockEd]

Hello,

I have a combobox that links to a named range on my sheet that has a list of dates on it. The named range is called "year2".

The named range automatically updates when a new year starts if you need to know.

However I have tried a few tricks from google but cannot get the format right. All I want to show is the year in YYYY format (e.g. 2022).

However, this code only populates it with "1905"

Private Sub combo_year_fees_Change()

combo_year_fees.Value = Format(combo_year_fees.Value, "yyyy")

End Sub

and this code populates it with the correct date but in d/m/yyyy format:

Private Sub combo_year_fees_Change()
Dim myRowSource As String
myRowSource = Format(myRowSource, "yyyy")

End Sub

It is populated from the intialize sub like so:

Me.combo_year_fees.List = shDateTimes.Range("Year2").Value

I have left RowSource and ControlSource blank at the moment.
Thanks for your help in advance!

 

[DanteAmor]

on my sheet that has a list of dates on it

If you have dates and want only the year. You must extract the year from each date.

I don't understand why you have it in the change event, if you are going to load the combo with all the years of the dates, you should do it in the Activate event, for example:

Private Sub UserForm_Activate()
  Dim dic   As Object
  Dim c     As Range
  
  Set dic = CreateObject("Scripting.Dictionary")
  'only unique years
  For Each c In Range("year2")
    dic(Format(c.Value, "yyyy")) = Empty
  Next
  combo_year_fees.List = dic.keys
End Sub