ExcelBanter - Creating a List Box on the fly

Hi Ray

My understanding of your requirements is:

take random start date and present user with a list of 1st of months between
then and the finish date?

Will these dates spread over more than one calendar year? ie could they
have a start date of June 15th 2002 and an end date of August 15th 2003. Or
will they always be within 12 months of each other or? What would the first
available date be if the start date was June 15th - June 1st or July 1st?
and for the end date?

If the start date is in cell A6 and the end date is in cell A7 and you have
a userform called userform1, the following code makes a good stab at it and
you should be able to tweak it as required.

Sub macro1()
Dim startmonth, endmonth As Integer
Dim x As Integer 'counter variable
Dim months(11) As String 'array for month names
Dim rollover As Boolean ' used to loop if start month is later than
endmonth

'fill the array of month names
months(0) = "January"
months(1) = "February"
months(2) = "March"
months(3) = "April"
months(4) = "May"
months(5) = "June"
months(6) = "July"
months(7) = "August"
months(8) = "September"
months(9) = "October"
months(10) = "November"
months(11) = "Decemeber"

'get the start and end months
startmonth = Month(Range("a6"))
endmonth = Month(Range("a7"))

'set rollover as applicable
rollover = (startmonth endmonth)

'pop a box up with the "read in" start and end months
MsgBox ("Start month = " & startmonth & " and end month = " & endmonth)

'empty the list before starting
UserForm1.ListBox1.Clear

'loop through months adding them to the listbox as required
x = startmonth

'x<=endmonth will be final stop case as rollover will definitely be(or
become) false
While ((x <= endmonth) Or (rollover))
'add the current month to the list box
UserForm1.ListBox1.AddItem months(x - 1)

'increment the month we're looking at
x = x + 1

'check to see if we need to begin again in January (ie we've hit
December and
'there's still more to do (rollover would be true)
If ((x 11) And (rollover)) Then
'as there's more to do, set x = 1 to start at
January
x = 1
'and set rollover to false so that we don't loop
forever
rollover = False
End If
Wend

'show the form with the relevant months being available
UserForm1.Show

End Sub

Hope this is of use

David

"Ray Kanner" wrote in message
...
I have a start date and and an end date as ranges in a
worksheet and would like the user to input a date in
between the start end and dates, with the only allowable
dates being the first of each month. Can I create a list
box that would have the permissible dates and have the
user choose one? Is this doable on the fly, since the
start and end dates frequently change? Would someone have
some sample code?

Thanks in advance

Ray Kanner