Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a List Box on the fly
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help Creating a Drop Down List from a List in another worksheet | Excel Discussion (Misc queries) | |||
Creating a Detailed List from a Summary List | Excel Worksheet Functions | |||
Creating A Breakout List from a Summary List | Excel Discussion (Misc queries) | |||
Creating a List based on your choice from Another List | Excel Discussion (Misc queries) | |||
Creating a list from an existing list. | Excel Worksheet Functions |