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]

Try

Dim cbblist As String, lr As Long
lr = Cells(Rows.Count, "I").End(xlUp).Row
cbblist = Sheets("sheet2").Range("I1:I" & lr)
Sheets("sheet1").cbb1.ListFillRange = "=cbblist"

The code has no errors. But after running them, the combo box is still empty and doesn't show the items under column I.

 

[Michael M]

IS the ComboBox actually named cbb1 ?

 

[dcmb]

Yes the combox box is named cbb1.

 

[Michael M]

This code works for me !
Chack the combo box properties and make sure the name is changed there as well.

Sub MM()
Dim cbblist As Range, lr As Long
lr = Cells(Rows.Count, "I").End(xlUp).Row
Set cbblist = Sheets("sheet2").Range("I1:I" & lr)
Sheets("sheet1").cbb1.ListFillRange = "=cbblist"
End Sub

 

[dcmb]

Everything is correct. And the codes run fine and successfully. But my combo box is till empty after running the codes.

I'm using ActiveX control for the combo box. Is that ok? Or should I use Form control?

 

[Michael M]

Create a named range for the data in sheet2 as cbblist
and rerun the code

 

[dcmb]

Create a named range for the data in sheet2 as cbblist
and rerun the code

works now. Thank you.

Would like to understand the code. May I know what does "lr = Cells(Rows.Count, "I").End(xlUp).Row" do? Are you asking VBA to count the no. of rows in column I?

Thanks in advance.

 

[Michael M]

Yes, it counts the cells that have something in them from the bottom of the sheet up to that last used row, and down from row 1

 

[dcmb]

Yes, it counts the cells that have something in them from the bottom of the sheet up to that last used row, and down from row 1

Thank you. Is there a way not to do the same without using a named range?

 

[Michael M]

See both methods of doing it

Private Sub fillit() 'list by cell method
Dim cell As Range, lr As Long
lr = Sheets("Sheet2").Cells(Rows.Count, "I").End(xlUp).Row
With Worksheets("Sheet1").OLEObjects("cbb1").Object
    .Clear
        For Each cell In Sheets("Sheet2").Range("I1:I" & lr)
            .AddItem cell.Value
        Next cell
End With
End Sub

OR


Private Sub MM() 'named range method
Dim cbblist As Range
Sheets("sheet1").cbb1.ListFillRange = "=cbblist"
End Sub