Populating Combo Box with column

Question

I have been smashing my head through the computer screen trying to solve this but every solution I've found led to nowhere.

I am trying to create a Userform and I want to populate a Combobox with data from the Excel sheet it's connected to. The Sheet is titled as 'PrinterModels (Printer Models)' in the VBA editor, the box is titled as cmbModel. When I populate the box with RowSource in it's properties it populates but when items are selected they disappear from the list when another item is selected. What I need is a way to populate the Box in code and if it acts the same, I need a way to repoplulate the box next time it is messed with. I've posted my latest attempt below which gives error 1004

Private Sub UserForm_Initialize()
    'cmbModel.List = PrinterModels.Range("A2:A").Value
    
    ModelLast = PrinterModels.Cells(Rows.Count, 1).End(x1Up).Row
    
    For i = 1 To ModelLast
    Value = PrinterModels.Cells(i, 1).Value
    cmbModel.AddItem Value
    Next i
End Sub

Did you set the rowsource to a range on a sheet? I fought with this recently and that's how I resolved it. If you need to add items then add them to the list on the sheet and update the resource with the expanded range. — bugdrown, Dec 23 '21 at 00:28
`x1Up` should be `xlUp` You can avoid this type of error almost completely by using `Option explicit` — Tim Williams, Dec 23 '21 at 00:33

score 0 · Answer 1 · answered Dec 23 '21 at 02:29

Okay, So I found an answer after continuing to hunt deeper and found a solution here StackOverflow 18088729

After changing the answer code to the following, the Box filled the way I wanted.

Private Sub UserForm_Initialize()
    'cmbModel.List = PrinterModels.Range("A2:A").Value
    With PrinterModels
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    
    For i = 1 To LastRow
    myValue = PrinterModels.Cells(i, 1).Value
    cmbModel.AddItem myValue
    Next i
End Sub

Populating Combo Box with column

1 Answers1