There are many different ways to populate the data in a combobox in a Userform – using lists on a lookup tab, defining each single one in hard code or running through a column to look for specific values and when finding said value add an item offset from that column etc. However, one of the most useful ways we have been using in our projects is to contain all the items you want to see in your dropdown and put them in 1 cell, all separated by a comma, or any other special character you want.
The example code below shows how to populate a combobox named csv1 in a userform at the UserForm Initialize event. It is quick and effective and the project we have been using this in has meant we can eradicate nearly 70% of the data from the clients database where duplicate entries were needed to facilitate lookups.
Private Sub UserForm_Initialize()
Dim csvlist As String, datanology, u As Long
csvlist = worksheets("Lookups").Range("A1")
csvlist = Trim(Mid(csvlist, 1))
datanology = Split(csvlist, ",")
For u = 0 To UBound(datanology)