patternMinor
Populating Day/Month Selections in UserForm Controls
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?
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 SubSolution
Why not doing it more directly like this:
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
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 SubI'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 SubContext
StackExchange Code Review Q#113899, answer score: 5
Revisions (0)
No revisions yet.