Search code examples
arraysexcelvbacombobox

How do I populate combobox lists from arrays in sequence

I have a userform that has several combo boxes with lists. These lists are to be fed from several dynamic arrays on a worksheet. I know how to fill one combobox with the array but I would like to fill each with their respective information from the arrays. I have the code to load one but I am thinking that if I were to re-write the code for each then the code would be very slow and tedious. Can you tell how to more effectively do this? enter image description here

The code to fill the Device Brand/Model combobox is as follows:

Private Sub UserForm_Initialize()
Dim machbrandmod As Range

Dim ws As Worksheet
Set ws = Worksheets("Arrays")

For Each machbrandmod In ws.Range("mach_type")
Me.nd_mod_cb.AddItem machbrandmod.Value
Next machbrandmod

End Sub

enter image description here

Do I have to exit sub, define the next Dim, and then rewrite the code for the next combobox? Or is there a more efficient way?

Thanks for all the help.

Solution

  • Supposing that your ranges looks like in your picture, meaning some rows of the same column (something like "A2:A6"), a simple solution will be the next:

    Dim arr As Variant
arr = ws.Range("mach_type").value: Me.nd_mod_cb.list = arr

    If your range has only a row and more columns (something like "A1:E1") you can use:

     arr = ws.Range("mach_type").value: Me.nd_mod_cb.list = WorksheetFunction.Transpose(arr)