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 to fill a combo box with VBA. 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. This can be very useful when you are wanting to
The following is the Excel VBA code to fill a combo box. Put this VBA code in a normal module;
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
cb.LinkedCell = "D1"
End Sub
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.
