Days in Month

[Aussiebear]

I'm looking to design a data entry form to allow a user to enter financial data. The user gets to select the required Month and Year, and I would like the form to then display the required number of days per month running in sequential order down the form.

I could set up a list of days per month for all months of the year to which the form could refer to, but this would not take into effect a "leap" year. Does Excel know if a year is a leap year or do I have to tell it so?

[Bob Phillips]

You don't say where they go, so I assumed a listbox.

I am also assuming you have a combobox with month names in and another with your selectable years in.

[vba][

Dim i As Long
Dim mpDate As Date

With Me

.ListBox1.Clear
For i = 1 To 31

mpDate = DateValue(i & "-" & cmbMonth.Value & "-" & .cmbYear.Value)
.ListBox1.AddItem Format(mpDate, "dd mmm yyyy")
If Month(mpDate) <> Month(mpDate + 1) Then Exit For
Next i
End With
[/vba]

[RonMcK3]

Aussiebear,

If you have the Analysis Pack installed, try using EOMONTH(start_date,months), which gives the EOM date that is 'months' before or after the start date.

Or you can do hand-to-hand combat with the code and get the end date by calculating the first of the following month and subtracting 1.

Cheers!

PS Now, that I see El Xid's solution, I like it much more. ;-)

[Aussiebear]

Thankyou XLD & Ron