Fill Active X Combo Box with Excel VBA
You can fill an Active X combo box with VBA based on an Excel dynamic range. This article will show you how. The advantage of this method is that if you update a list this particular code will update the Combo box based on the new list. You can also use VBA to set the position of the combo box on the sheet. In the image below you can see that the combo box has been placed on Row 10 exactly between the two columns.
The following is the code;
Sub ComboBoxFil() 'Fill a combo box with Excel VBA
Dim cb As Object
Dim rng As Range
Set rng=Range("D10:E10") 'Range to populate
Set cb=ActiveSheet.DropDowns.Add(rng.Left, rng.Top, rng.Width, rng.Height)
cb.ListFillRange="A10:A" & Range("A" & Rows.Count).End(xlUp).Row
The line to set where the combo box appears is the Dropdowns.Add line. This ensures the active X combo box appears neatly between D10 and E10. The following file outlines this Excel VBA procedure using the above combo box.