CODEWITHSUNDEEP
vbaexcelcombobox
Chris
Chris

Reputation: 95

Filling combobox with range and remove blanks

I thought this would be simple but for whatever reason, my combobox is always empty and I don't know why! In column B I have text from cell B3 downwards and want this list to populate my combobox (called ComboBox1). Some cells in column B are empty and I do not want these in the combobox so this is the code I have:

Private Sub CompanyList()
Dim c As Range
With Worksheets("Database")
    For Each c In Worksheets("Database").Range("B3", .Range("B" & Rows.Count).End(xlUp))
        If c.Value <> "" Then ComboBox1.AddItem c.Value
    Next c
End With
End Sub

My sheet is called Database, the ActiveX combobox is on the same sheet and it's called ComboBox1. Where am I going wrong here?

UPDATE

I've changed the code so that it runs whenever I update a cell so

Private Sub Worksheet_Change(ByVal Target As Range)

However, now when I update a cell, it will add the whole of column B in to the combobox again. It's not adding blank cells and it's adding values from underneath blank cells which is good, but it's just adding everything twice over whenever I make a change.

Although thinking about it, I'm not sure why I thought otherwise. However, if I change a company name in the list, I still need the combobox to update in livetime.

Upvotes: 1

Views: 15849

Answers (1)

R3uK
R3uK

Reputation: 14547

Few corrections around the With statements, to properly define the range :

Private Sub CompanyList()
Dim c As Range
ComboBox1.Clear
With Worksheets("Database")
    For Each c In .Range(.Range("B3"), .Range("B" & .Rows.Count).End(xlUp))
        If c.Value <> vbNullString Then ComboBox1.AddItem c.Value
    Next c
End With
End Sub

Upvotes: 1

Related Questions