Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Month Names as ComboBox List
Hello!,
I have dates in Column A of sheet1 I want add month names as my combobox1 list but to include only the months available in column A for eg: if the first date is 16/6/06 and last date is 1/10/06 then i want only June thru october to show in the list.. How can i do this thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Month Names as ComboBox List
Abdul,
assuming you a suitable range named "MyDates" Private Sub CommandButton1_Click() Dim StartMonth As Long Dim EndMonth As Long Dim i As Long StartMonth = Month(Application.WorksheetFunction.Min(Range("MyD ates"))) EndMonth = Month(Application.WorksheetFunction.Max(Range("MyD ates"))) With ComboBox1 .Clear For i = StartMonth To EndMonth .AddItem MonthName(i) Next End With End Sub NickHK "Abdul" wrote in message ps.com... Hello!, I have dates in Column A of sheet1 I want add month names as my combobox1 list but to include only the months available in column A for eg: if the first date is 16/6/06 and last date is 1/10/06 then i want only June thru october to show in the list.. How can i do this thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Month Names as ComboBox List
Private Sub CommandButton1_Click()
Dim i As Long For i = Evaluate("MIN(IF(A1:A1000<"""",MONTH(A1:A1000)))" ) To _ Evaluate("MAX(IF(A1:A1000<"""",MONTH(A1:A1000)))" ) ComboBox1.AddItem Format(DateSerial(Year(Date), i, 1), "mmmm") Next i End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Abdul" wrote in message ps.com... Hello!, I have dates in Column A of sheet1 I want add month names as my combobox1 list but to include only the months available in column A for eg: if the first date is 16/6/06 and last date is 1/10/06 then i want only June thru october to show in the list.. How can i do this thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Month Names as ComboBox List
If you only want to show the months listed, not from the first to the last
month, then this process scans the list and adds each new month found to an array, this array is then loaded into the combo control. Option Base 1 Sub months_load() Dim xlr As Long, xr As Long, xFound As Boolean Dim xMonth As Date, m As Integer, i As Integer m = 1 Dim zArray() As Date ReDim Preserve zArray(m) With Sheets("Sheet1") xlr = .Cells(.Rows.Count, 1).End(xlUp).Row For xr = 1 To xlr If IsDate(.Cells(xr, 1)) Then xMonth = DateSerial(Year(.Cells(xr, 1)), Month(.Cells(xr, 1)), 1) ' check if month in array xFound = False For i = LBound(zArray) To UBound(zArray) If xMonth = zArray(i) Then xFound = True Exit For End If Next i ' entry not found so add it If Not xFound Then zArray(m) = xMonth m = m + 1 ReDim Preserve zArray(m) End If End If Next xr ' fill the control .ComboBox1.Clear For i = LBound(zArray) To UBound(zArray) - 1 .ComboBox1.AddItem Format(zArray(i), "mmm-yyyy") Next i End With End Sub -- Cheers Nigel "Abdul" wrote in message ps.com... Hello!, I have dates in Column A of sheet1 I want add month names as my combobox1 list but to include only the months available in column A for eg: if the first date is 16/6/06 and last date is 1/10/06 then i want only June thru october to show in the list.. How can i do this thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
return a list of names, from a large list of repeated names. | Excel Worksheet Functions | |||
Tying names to different values month to month. | Excel Worksheet Functions | |||
create a random list of names from a list of names in EXCEL | Excel Worksheet Functions | |||
ComboBox list reliant on the entry from a different ComboBox | Excel Programming | |||
find names on list 1 in list 2. list 1 4000 names list 2 400 name | Excel Worksheet Functions |