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.

 

[Legacy 143009]

Like this?

Private Sub UserForm_Initialize()
  Dim LR As Long
  With Sayfa1
  LR = .Range("G" & Rows.Count).End(xlUp).Row
  For i = 2 To LR - 1
    For j = i + 1 To LR
      If DateSerial(Year(.Range("G" & i).Value2), Month(.Range("G" & i).Value2), 1) = DateSerial(Year(.Range("G" & j).Value2), Month(.Range("G" & j).Value2), 1) Then
        GoTo Skip
      End If
    Next
    ComboBox1.AddItem UCase(Format(.Range("G" & i).Value2, "mmm-yyyy"))
Skip:
  Next
  End With
End Sub

 

[Legacy 143009]

A shorter version would be:

Private Sub UserForm_Initialize()
  Dim LR As Long
  With Sayfa1
  LR = .Range("G" & Rows.Count).End(xlUp).Row
  For i = 2 To LR
    For j = i + 1 To LR
      If Format(DateValue(.Range("G" & i).Value), "mmm-yyyy") = Format(DateValue(.Range("G" & j).Value), "mmm-yyyy") Then
        GoTo Skip
      End If
    Next
    ComboBox1.AddItem UCase(Format(.Range("G" & i).Value, "mmm-yyyy"))
Skip:
  Next
  End With
End Sub

 

[KalilMe]

thanks. I use second version, but I have some problems:
could declare variable ,please ?
because of I have others codes in the form and I have to use option explicit
and your code asks me declare some variables.
another thing I want removing duplicates dates in combobox because it repeats dates for the same month as I said in OP

with considering there is duplicates dates for the same month then should not repeat MM-YYYY based on column G

I no know if this sentence is clear for you .

 

[Legacy 143009]

I think this should work. I declared no more variables than i and j. Yes, this code takes distinct dates:

Private Sub UserForm_Initialize()
  Dim LR As Long, i As Long, j As Long
  With Sheet1
  LR = .Range("G" & Rows.Count).End(xlUp).Row
  For i = 2 To LR
    For j = i + 1 To LR
      If Format(DateValue(.Range("G" & i).Value), "mmm-yyyy") = Format(DateValue(.Range("G" & j).Value), "mmm-yyyy") Then
        GoTo Skip
      End If
    Next
    ComboBox1.AddItem UCase(Format(.Range("G" & i).Value, "mmm-yyyy"))
Skip:
  Next
  End With
End Sub

 

[HaHoBe]

Hi KalilMe,

assuming the data in Column G are if type data:

Private Sub UserForm_Activate()
  Dim dTally      As Dictionary
  Dim rSource     As Range
  Dim c           As Range
  Dim sTemp       As String

  Set dTally = New Dictionary
  Set rSource = sheet1.Range("G2:G" & sheet1.Range("G" & sheet1.Rows.Count).End(xlUp).Row)
  For Each c In rSource
    sTemp = Format(c.Value, "MMM-YYYY")
    If Not dTally.Exists(sTemp) Then
      dTally.Add sTemp
    End If
  Next c
  ComboBox1.List = dTally.Keys
End Sub

Ciao,
Holger

 

[KalilMe]

@Flashbond
perfect ! many thanks

 

[KalilMe]

@HaHoBe
thanks
I look forward to fix error argument not optinal in this word .Add

 

[HaHoBe]

Hi KalilMe,

you will have to set a reference to Microsoft Scripting Runtime in the VBE / Tools / Options - sorry to miss that when posting.

Holger

 

[KalilMe]

I really did it before you say that, but the error still shows .