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
|