Load userform combox with list of next months starting from last month

[craigs23]

Hi All,

I have recieved som invaluable help on this forum so thanks for that.

Hopefully someone can also help me with my current conundrum,

I have a userform with a combobox which when it initializes it populates the combobox with the current month and the next 12 months in advance based on todays date.

However what Iideally want is for the combobx to list to start with the previous month.

for example the if i run the code (see below) in it's current format it the combobox lists

Aug - 14 thru to Jul - 15

What I actually want it to do is to list

July - 14 thru to Jun-15

I have tried to change the code but to no avail can any one help?

Thanks in advance

Private Sub UserForm_Initialize()
Dim MyDate As Date
Dim i As Integer
MyDate = Date
For i = 1 To 12
Me.cboMonth.AddItem Format(MyDate, "mmm yyyy")
MyDate = DateAdd("m", 1, MyDate)
Next
End Sub

 

[Somendra Misra]

Hi,

Just added EDate function in your code. Try this:

Private Sub UserForm_Initialize()
Dim MyDate As Date
Dim OldDate As Date
Dim i As Integer
MyDate = Date
OldDate = Application.WorksheetFunction.EDate(MyDate, -1)
For i = 1 To 12
Me.ComboBox1.AddItem Format(OldDate, "mmm yyyy")
OldDate = DateAdd("m", 1, OldDate)
Next
End Sub

Regards,

 

[Hui]

Try:

Private Sub UserForm_Initialize()
Dim MyDate As Date
Dim i As Integer
MyDate = DateSerial(Year(Date), Month(Date) - 1, Day(Date))
For i = 1 To 12
  Me.cboMonth.AddItem Format(MyDate, "mmm yyyy")
  MyDate = DateAdd("m", 1, MyDate)
Next
End Sub

 

[craigs23]

Thanks to Both of you for your quick replies.
Both solutions work perfectly!
Thanks again