Trying to Create a Dynamic Loop for a Combo Box

[jlathem]

Can someone take a look at this code and let me know what I am doing wrong?

I am trying to create a dynamic list of values for a Combo Box to list week numbers from the Start_Week to the End_Week and display each week number in the combo box as an option to select.

Any help is much appreciated,
James

Private Sub Combo_Week_Number_GotFocus()
    'Declare Variables
    Dim Start_Week As Integer
    Dim End_Week As Integer
    Dim counter As Integer
 
    'Give Variables Value
    Start_Week = DatePart("ww", #9/1/2010#, [vbSaturday], [vbFirstJan1])
    End_Week = DatePart("ww", Now(), [vbSaturday], [vbFirstJan1])
 
    'Create Loop
    For counter = Start_Week To End_Week
        Me.Combo_Week_Number = counter
    Next
End Sub

 

[MajP]

Me.Combo_Week_Number.rowsourcetype = "Value List"
For counter = Start_Week To End_Week
Me.Combo_Week_Number.addItem(counter)
Nex

 

[MajP]

and another way

Dim strDates As String

For counter = Start_Week To End_Week
If strDates = "" Then
strDates = CStr(counter)
Else
strDates = strDates & ";" & counter
End If
Next
me.Combo_Week_Numbe.RowSource = strDates
End Sub

 

[jlathem]

Hi MajP,

Thank you for your reply and the directions!

I knew there had to be a something like the .addItem but didn’t know enough about VBA to know what to ask for.

The second way is interesting and I will save both of the pieces of code for future reference too.

Thanks again,
James