Populate combo box for year from dates in a column

[lex_m]

I know how to populate a combo box like this, where I have years listed in this range like 2005, 2006……..

Me.CBYear.List = Sheet2.Range("G1:G10").Value

I have dates in column A, Is there anyway to populate it using the dates that are in A1 : A??, the last row with a date in it, for the first year in the column to whatever year it is now?
So if I had dates starting with 2-25-2005 the combo box would populate with 2005, 2006, 2007 ……. 2011

this is for excel 2003
Thanks

 

[AnAnalyst]

Hi there,

How about something like this:

(Remove the space between Button and Click!)

Private Sub ComboBox1_DropButton Click()
    Dim rngyears As Range, lnglrow As Long, intstartyear As Integer
    Application.EnableEvents = False
    lnglrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    Set rngyears = ActiveSheet.Cells(2, 1).Resize(lnglrow, 1)
    intstartyear = Year(WorksheetFunction.Min(rngyears))
    arryears = Array( _
                intstartyear, _
                intstartyear + 1, _
                intstartyear + 2, _
                intstartyear + 3, _
                intstartyear + 4, _
                intstartyear + 5)
    ComboBox1.List = arryears
    Set rngyears = Nothing
    Application.EnableEvents = True
End Sub

This checks the data for the minimum year, loads an array with six years starting with the earliest year, and updates the combobox every time the drop-button is clicked.

 

[AnAnalyst]

... or (now I've read your post again) ...

(Again, remove the space between Button and Click)

Private Sub ComboBox1_DropButton Click()
    Dim rngyears As Range, lnglrow As Long, intstartyear As Integer, intendyear As Integer
    Application.EnableEvents = False
    lnglrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    Set rngyears = ActiveSheet.Cells(2, 1).Resize(lnglrow, 1)
    intstartyear = Year(WorksheetFunction.Min(rngyears))
    intendyear = Year(Date)
    j = 0
    ReDim arryears(intendyear - intstartyear)
    For i = intstartyear To intendyear
        arryears(j) = i
        j = j + 1
    Next i
    ComboBox1.List = arryears
    Set rngyears = Nothing
    Application.EnableEvents = True
End Sub

 

[lex_m]

Thanks that works, forgot one thing, I am using a change event on the combo box and need it to have All Dates as one of the things in the list, so is there a way to have All Dates to be put in at the top of the box also?
Tried ComboBox1.List ="All Dates" & arryears but That did not do it

 

[lex_m]

found one way

With ComboBox1
.AddItem "All Years"
End With

 

[AnAnalyst]

You could add the "All years" entry to the start of the array if you want it to appear at the top of the list:

Private Sub ComboBox1_DropButton Click()
    Dim rngyears As Range, lnglrow As Long, intstartyear As Integer, intendyear As Integer
    Application.EnableEvents = False
    lnglrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    Set rngyears = ActiveSheet.Cells(2, 1).Resize(lnglrow, 1)
    intstartyear = Year(WorksheetFunction.Min(rngyears))
    intendyear = Year(Date)
[COLOR=darkgreen]    j = 1
    ReDim arryears(1)
    arryears(0) = "All years"
    ReDim Preserve arryears(intendyear - intstartyear + 1)[/COLOR]
    For i = intstartyear To intendyear
        arryears(j) = i
        j = j + 1
    Next i
    ComboBox1.List = arryears
    Set rngyears = Nothing
    Application.EnableEvents = True
End Sub

 

[lex_m]

Thanks