Modifying ListBox using VBA

Question

I'm very new to VBA and I've been trying to find a way to create a date selection drop-down but I haven't been successful in handling a "February" selection. Here is what I have so far,

If (monthListBox.Value = "Jan" Or monthListBox.Value = "Mar" Or monthListBox.Value = "May" Or monthListBox.Value = "Jul" Or monthListBox.Value = "Aug" Or monthListBox.Value = "Oct" Or monthListBox.Value = "Dec") Then
    If dayCount < 31 Then
        For i = dayCount To 31
            dateComboBox.AddItem (i)
        Next i
    End If
ElseIf (monthListBox.Value = "Apr" Or monthListBox.Value = "Jun" Or monthListBox.Value = "Sep" Or monthListBox.Value = "Nov") Then
    dateComboBox.RemoveItem (30)
    If dayCount < 30 Then
        For i = dayCount To 30
            dateComboBox.AddItem (i)
        Next i
    End If
Else
    If isLeapYear = True Then
        With dateComboBox
            .RemoveItem (30)
            .RemoveItem (29)
        End With
    Else
        dateComboBox.RemoveItem (28)
    End If
End If

What exactly are you having troubles handling? What is the expected outcome and what outcome are tou getting? — Samuel Everson
– Samuel Everson, Commented Apr 13, 2020 at 22:09

Dy.Lee · Accepted Answer · 2020-04-14 13:01:28Z

1

Try,

It would be easier for you to apply the calculated date system to the system than to calculate the leap year.

Private Sub UserForm_Initialize()
    Dim y As Integer
    Dim i As Integer, s As String

    y = 2020 '<~~ your year

    For i = 1 To 12
        s = Format(DateSerial(y, i, 1), "mmm")
        monthListBox.AddItem s
    Next i

End Sub

Private Sub monthListBox_Click()
    Dim s  As String
    Dim y As Integer
    Dim st As Double, et As Double
    Dim i As Integer, cnt As Integer

    y = 2020 '<~~ your year

    s = monthListBox.Value
    st = DateValue(y & "/" & s & "/" & 1)
    et = DateAdd("m", 1, st)
    cnt = et - st

    dateComboBox.Clear
    For i = 1 To cnt
        dateComboBox.AddItem i
    Next i


End Sub

Image

edited Apr 14, 2020 at 13:01

answered Apr 14, 2020 at 2:21

Dy.Lee

7,5471 gold badge14 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

HTH Over a year ago

just to throw in that the list of months names is a built in function MonthName() so that the code inside the first loop can simply be monthListBox.AddItem MonthName(i) (or monthListBox.AddItem Left$(MonthName(i),3), to keep first three letters only)

Dy.Lee Over a year ago

@HTH, In my country it is a different character. Anyway, thanks for the new function.

Dy.Lee Over a year ago

@HTH, In my country MonthName(1) is "1월".

Collectives™ on Stack Overflow

Modifying ListBox using VBA

1 Answer 1

Image

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Image

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related