How to generate a list with the date of every other Friday.

[TheThird152]

Greetings!

I'm brand new to this board and just beginning to learn VBA, so please bear with me. I'm running Windows XP Pro and I use Excel 2003 & 2007, but am using 2003 for this project.

Ok, here's the scenario. At work, I've been asked to redesign a timesheet for our hourly employees. I'm probably overdoing it, but it is a great opportunity for me to learn some VBA. I've got most of my programming done, but I could use some help figuring out something that has to do with a date.

On my timesheet, there is a button that, when clicked, allows you to select a pay period end date. Our pay periods end every other Friday. When the button is clicked, it opens a form I designed that has a listbox that I manually entered every pay period for the year into. Here is the code I used:

Private Sub UserForm_Initialize()
' Populate the ListBox control.
listPayPeriodEndDates.AddItem "04/03/09"
listPayPeriodEndDates.AddItem "04/17/09"
listPayPeriodEndDates.AddItem "05/01/09"
listPayPeriodEndDates.AddItem "05/15/09"
listPayPeriodEndDates.AddItem "05/29/09"
listPayPeriodEndDates.AddItem "06/05/09"
listPayPeriodEndDates.AddItem "06/19/09"
listPayPeriodEndDates.AddItem "07/03/09"
listPayPeriodEndDates.AddItem "07/17/09"
listPayPeriodEndDates.AddItem "07/31/09"
listPayPeriodEndDates.AddItem "08/07/09"
listPayPeriodEndDates.AddItem "08/21/09"
listPayPeriodEndDates.AddItem "09/04/09"
listPayPeriodEndDates.AddItem "09/18/09"
listPayPeriodEndDates.AddItem "10/02/09"
listPayPeriodEndDates.AddItem "10/16/09"
listPayPeriodEndDates.AddItem "10/30/09"
listPayPeriodEndDates.AddItem "11/13/09"
listPayPeriodEndDates.AddItem "11/27/09"
listPayPeriodEndDates.AddItem "12/04/09"
listPayPeriodEndDates.AddItem "12/18/09"
listPayPeriodEndDates.AddItem "01/01/10"
' Select the default value
listPayPeriodEndDates.Value = "04/03/09"
End Sub

What I'd like to do instead is have VBA populate the listbox (or combobox or what have you) dynamically, by using a reference point (say, the first pay period of the year) and then populating every other Friday from that point forward. Also, it would be great if it could reference the current date as to only list pay period end dates in the future (or even the two prior to todays date, and then maybe 3 or 4 pay period end dates in the future).

I'd certainly appreciate any input anyone might have, I'm open to all ideas. Also, it's no big hurry, I'll keep working on it in the meantime.

Thanks in advance for any help!

Paul

 

[Bill Rockenbach]

Thanks Norie