HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

Populating Day/Month Selections in UserForm Controls

Submitted by: @import:stackexchange-codereview··
0
Viewed 0 times
controlsuserformpopulatingselectionsmonthday

Problem

So I have a simple userform where I require the User to input the target month, and the day to analyse data up to.

This is just a small section of code to govern populating the month selection and then determining days based on the month.

Particular questions:

Is this a smart way to handle dates generally?

Is this a good way to ensure that the User cannot enter a non-existent date?

Any edge cases I haven't considered?

And, as always, if you were handed this code to maintain, what would you be thinking as you read through it?

Private Sub UserForm_Initialize()

    PopulateMonthBox Me.UF_BankRec_cbx_Month

End Sub

Private Sub PopulateMonthBox(ByRef monthBox As MSForms.ComboBox)

    Dim ix As Long
    Dim monthText As String

        For ix = 1 To 12
            monthText = MonthName(ix)
            monthBox.AddItem monthText
        Next ix

End Sub

Private Sub UF_BankRec_cbx_Month_Change()

    Dim dayBox As MSForms.ComboBox
    Set dayBox = Me.UF_BankRec_cbx_EndDay

    Dim monthBox As MSForms.ComboBox
    Set monthBox = Me.UF_BankRec_cbx_Month

    Dim monthText As String
        monthText = monthBox.Text

        dayBox.Clear
        PopulateDayBox dayBox, monthText

End Sub

Private Sub PopulateDayBox(ByRef dayBox As MSForms.ComboBox, ByVal monthText As String)

    Dim ix As Long, dayLimit As Long
    Dim ixMonth As Long, ixNextMonth As Long
    Dim ixYear As Long
    Dim firstDayNextMonth As Date, lastDayThisMonth As Date

        ixMonth = Month("01/" & monthText & "/2000")
        ixNextMonth = ixMonth + 1
        ixYear = Year(Now)

        firstDayNextMonth = DateSerial(ixYear, ixNextMonth, 1)
        lastDayThisMonth = DateAdd("d", -1, firstDayNextMonth)

        dayLimit = Day(lastDayThisMonth)

        For ix = 1 To dayLimit
            dayBox.AddItem ix
        Next ix

End Sub

Solution

Why not doing it more directly like this:

Private Sub UserForm_Initialize()

  Dim i As Byte
  For i = 1 To 12
    UF_BankRec_cbx_Month.AddItem MonthName(i)
  Next

End Sub

Private Sub UF_BankRec_cbx_Month_Change()

  Dim i As Byte, startDate As Long
  UF_BankRec_cbx_EndDay.Clear

  startDate = DateValue("01/" & UF_BankRec_cbx_Month.Text & "/" & Year(Now()))

  While Month(startDate) = Month(startDate + i)
    i = i + 1
    UF_BankRec_cbx_EndDay.AddItem i
  Wend

End Sub


I'd say it is a bit better to read while i normally just speed codes up (so they end up in a way you can't read them anymore)... I tried my best to make it readable even without comments.
However, there may be a reason for you to run extra macros and using objects which i skipped out here... (to insert arrays to CBoxes is also possible solution to avoid objects)

If there any questions or something like that, feel free to ask Or better, tell me what you don't like looking at my code (this way i get the best idea what you desire) :)

At least your lastDayThisMonth = DateAdd("d", -1, firstDayNextMonth) is the same like lastDayThisMonth = firstDayNextMonth - 1 (but i am sure there is also a reason for that)

Code Snippets

Private Sub UserForm_Initialize()

  Dim i As Byte
  For i = 1 To 12
    UF_BankRec_cbx_Month.AddItem MonthName(i)
  Next

End Sub

Private Sub UF_BankRec_cbx_Month_Change()

  Dim i As Byte, startDate As Long
  UF_BankRec_cbx_EndDay.Clear

  startDate = DateValue("01/" & UF_BankRec_cbx_Month.Text & "/" & Year(Now()))

  While Month(startDate) = Month(startDate + i)
    i = i + 1
    UF_BankRec_cbx_EndDay.AddItem i
  Wend

End Sub

Context

StackExchange Code Review Q#113899, answer score: 5

Revisions (0)

No revisions yet.