combo box.

[dcmb]

I googled but couldn't find an answer.

I'm still pretty new to VBA. And I want to use a combo box / list box to show the items in a column which I can select.

For example:

         A
1     apple
2     orange
3     apple
4     banana

I want the combo box / list box to show the list of items in column A. And say, I select orange on the combo box / list box then I want to use the selected data for filtering.

All suggestions and ideas are welcomed.

Thanks in advance.

 

[dcmb]

Thank you very much Michael. The alternative codes work great too.

I moved the ".Clear" up in line with "With Worksheets("Sheet1").OLEObjects("cbb1").Object" the code will fail.

i.e. instead of:

With Worksheets("Sheet1").OLEObjects("cbb1").Object
    .Clear

I tried:

With Worksheets("Sheet1").OLEObjects("cbb1").Object.Clear

Any idea what's wrong? And what's the ".Clear" for?

And for

For Each cell In Sheets("Sheet2").Range("I1:I" & lr)
            .AddItem cell.Value
        Next cell

Are we telling VBA to add the value of the cell into the combox box for every cell from in column I? And it will do so until the last row with data in column I because we assigned it to the variable "lr" which counts the number of rows that has something in them?

 

[Michael M]

The .Clear can't go on the same line...it's not an object ...it's an action.
what it's doing is telling Excel to clear that ComboBox of ALL entries, so it can start fresh each time.
Otherwise, if you added 1 more item to the list, it would add it into the combobox, but would duplicate all the other items as well.

Are we telling VBA to add the value of the cell into the combox box for every cell from in column I? ..... YES
And it will do so until the last row with data in column I because we assigned it to the variable "lr" ..... YES
which counts the number of rows that has something in them? YES

 

[dcmb]

The .Clear can't go on the same line...it's not an object ...it's an action.
what it's doing is telling Excel to clear that ComboBox of ALL entries, so it can start fresh each time.
Otherwise, if you added 1 more item to the list, it would add it into the combobox, but would duplicate all the other items as well.

Many thanks Michael.