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

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

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

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