Selected Answer
Abdo
Here's a solution using a dictionary (like you did) but making each dictionary entry a string array. This avoids the complications of nesting dictionaries but assumes that BATCH (column B) is a unique key. It will give an error message if it finds a duplicate when creating the dictionary (where dic is a Public variable).
The attached file still uses your bubble sort Function mySort(a) but has the modified version of your Initilaize sub below (main changes in bold):
Private Sub UserForm_Initialize()
Dim ValArr As Variant, i As Long, ii As Long, temp As Variant, a As Variant
Set dic = CreateObject("Scripting.Dictionary")
' create an array, Item to Qty
a = Sheets("PRICES").Cells(1).CurrentRegion.Value
For i = 2 To UBound(a, 1)
' set dictionary to all text
For ii = 2 To UBound(a, 2)
a(i, ii) = a(i, ii) & ""
Next ii
' create keys
If Not dic.exists(a(i, 2)) Then
' make entry an array of strings
dic(a(i, 2)) = Array(a(i, 3), a(i, 4), a(i, 5), a(i, 6))
Else
MsgBox "Cannot complete drop-down lists since Column B data includes duplicate(s) of " & a(i, 2)
Exit Sub
End If
Next i
'create alphabetical list of dictionary entries for BATCH
a = mySort(dic.keys)
' populate BATCH comboboxes
For i = 1 To 45 Step 4
Me("ComboBox" & i).List = a
Next
End Sub
For each BATCH (dictionary key), the dictionary entry is an array of values from the data in your worksheet. I've then removed your Private Sub GetList(CB) and replaced it with this code (with explanatory comments) which will complete a row once a Batch is picked and jump to the next Batch ComboBox:
Private Sub FillCBs(CB)
Dim Vals As Variant, i As Long, Rw As Long
' get the array from the dictionary entry
Vals = dic(Me("ComboBox" & CB).Value)
' loop and fill other CBs
For i = 1 To 3
With Me("ComboBox" & CB + i)
.List = Array(Vals(i))
.Value = .List(0)
End With
Next i
' determine the row in UserForm
Rw = 1 + (CB - 1) / 4
' Set Qty
Me("TextBox" & 17 + Rw).Value = Vals(4)
' set Item (as row)
Me("TextBox" & Rw).Value = Rw
' move to next Batch CB (unless the last)
If CB < 45 Then Me("ComboBox" & CB + 4).SetFocus
End Sub
This is dependent on your numbering of controls and uses arrays where the first entry is (1) rather than (0) - you will see that I've declared Option Base 1 (at the top of the UserForm codes) to get this.
Given all the values in a line are completed, you only need Change subs for the BATCH ComboBoxes, so:
Private Sub ComboBox1_Change()
FillCBs 1
End Sub
Private Sub ComboBox5_Change()
FillCBs 5
End Sub
up to ComboBox45.
The slight downside of this is that the form could be completed with duplicated batches by the user (unless you add a check when a Batch is selected or -easier- when you come to transfer the form to another worksheet).
Hope this fixes your problem.