The following is programming code we frequently use to populate a drop-down list or listbox on an Excel UserForm with a unique list of items from a spreadsheet.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
Private Sub UserForm1_Intialize() 'Creates a new collection Dim myList As New Collection 'Determines number of rows to loop through numRows = Range("A1").CurrentRegion.Rows.Count 'Optional - erases existing dropdown list from control Me.lstDropdown.Clear 'Loops through each row and adds to collection For i = 2 To numRows 'An item can only be added to a collection once, hence the on error On Error Resume Next myList.Add Cells(i, "A"), Cells(i, "A") On Error GoTo 0 Next 'Populates control with items from the collecton For i = 1 To myList.Count Me.lstDropdown.AddItem myList.Item(i) Next 'Optional - erases an existing value Me.lstDropdown.Value = "" End Sub |
