CODEWITHSUNDEEP
vbaexcelfor-loopcombobox
Peleus
Peleus

Reputation: 69

Use a For-loop to fill a Combobox

For a project I want to fill a Combobox on a Userform with a variable amount of options. The amount of options depends on an amount given earlier in the sheet. The user will input a value and assign names to all these values. It could be that there are just 2 names, but it could also be that there are 10 for example.

I would like the Combobox to use the value given (amount of names) to fill itself with the names, which are stored at different locations. My current code is presented below, but it gives me the following Compile error and then selects the .AddItem part as source of the error..

Compile error: Expected Function or variable

Private Sub UserForm_Initialize()
'Sheet1.Range("A1") contains the value for the amount of names

If Sheet1.Range("A1") = 0 Or Sheet1.Range("A1") = "" Then
    'Do Nothing
Else
    With ComboBox1
        For n = 1 To Sheet1.Range("A1")
            'col determines the column in which the names are found
            'The first name is in column 2, the next in column 10, etc.
            col = 2 + 8 * (n - 1)
            .AddItem = Sheet2.Cells(5, col)
        Next
    End With
End If

End Sub

Hopefully my problem is clear. I have the feeling that I am already very close to the answer, but I could not find it anywhere using Google..

Upvotes: 0

Views: 2766

Answers (2)

user3598756
user3598756

Reputation: 29421

as an alternative you could use the List property of ComboBox object and fill it via an array as follows:

Private Sub UserForm_Initialize()
    With Sheet1.Range("A1") 'reference the cell that contains the value for the amount of names
        If .Value > 0 Then Me.ComboBox1.List = GetValues(.Value) '<--| fill ComboBox via its 'List' property passing it the array returned by GetValues() function 
    End With
End Sub

Function GetValues(nCols As Long) As Variant
    Dim n As Long

    ReDim vals(1 To nCols) As Variant '<--| size the array to match the amount of names passed in
    For n = 1 To nCols
        vals(n) = Sheet2.Cells(5, 2 + 8 * (n - 1)) '<--| fill the array: the first name is in column 2, the next in column 10, etc.
    Next
    GetValues = vals '<--| return the filled array
End Function

which will also make your code more "modular"

Upvotes: 1

arcadeprecinct
arcadeprecinct

Reputation: 3777

.Additem is a method, not a property you can set. You have to provide the item as an argument, i.e.

.AddItem Sheet2.Cells(5, col)

Upvotes: 4

Related Questions