populate date (MM-YYYY) in combobox on userform

[KalilMe]

I want populating date in combobox on userform like this JAN-2022, FEB 2022 ......
JAN-2023, FEB-2023 ..... and so on based on column G
with considering there is duplicates dates for the same month then should not repeat MM-YYYY based on column G
here is what I have but doesn't work

Private Sub UserForm_Initialize()
Dim c As Range, LR As Long
LR = sheet1.Range("G" & Rows.Count).End(xlUp).Row
For Each c In sheet1.Range("G" & LR)
ComboBox1.AddItem Format(c.Value, "mm-yyyy")
Next c
End Sub

I hope somebody has idea to do that.

 

[HaHoBe]

Hi KalilMe,

change codeline to read

      dTally.Add sTemp, 1

and be aware that it's a String that is shown there. In order to use it as a Date you may use DateValue("01-" & ComboBox1.Value).

Holger

 

[KalilMe]

thanks it works excellently
about this

In order to use it as a Date you may use DateValue("01-" & ComboBox1.Value).

could show me where put this line ?
I still don't understand what could show .

 

[HaHoBe]

Hi KalilMe,

it depends on what you use the CB for. My remark is meant to be used as part of an AutoFilter for the data (such as filter the data to show all records for corresponding month).

I choose the Change-Event for the CB to trigger the code:

Private Sub ComboBox1_Change()
Dim dteStart As Date

dteStart = DateValue("01-" & ComboBox1.Value)
With Tabelle1
  If .AutoFilterMode Then .AutoFilterMode = False
  .Rows(1).AutoFilter Field:=7, _
                      Criteria1:=">=" & CLng(dteStart), _
                      Operator:=xlAnd, _
                      Criteria2:="<=" & CLng(DateSerial(Year(dteStart), Month(dteStart) + 1, 0))
End With
End Sub

On any change of the ComboBox the data in the worksheet is filtered accordingly.

Holger

 

[KalilMe]

@HaHoBe
I thought you mean I should just modify some line based on your suggestion , but I see this is different what I look for it.
thanks very much for your solution