Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

populate items for each other of them for four combo boxes

0

Hello

in  my  project  contains for  four  comboboxes for  each row  on userform  & one  textbox.

each  combobox depends on each other of them is  linked with columns B,C,D,E consecutively. as  to  textbox(QTY) is  linked with  column F 

for  instance (combobox1=column B,combobox2=column C,combobox3=column D,combobox4=column E,textbox18=column F

combobox5=column B,combobox6=column C,combobox7=column D,combobox8=column E,textbox19= column F 

and  so  on  for  each  row on userform contains  four  comboboxes  and one textbox are  linked  with columns B,C,D,E , F 

each  combobox  depends on each other of them after select one  by  one .

note: the column ITEM on userform contains multiple textbox for each row is not relating with the whole codes just to increment serial number when filled combo boxes.

so  far  gives error  object  required in  this  line 

If Not dic(a(i, 3)).exists(a(i, 4))

thanks

Answer
Discuss

Discussion

Abdo

Thanks for adding more details. Sorry but your approach of nesting dictionaries is involved and I just don't have time to work it all out. Hope someone else replies.
John_Ru (rep: 6102) Jun 8, '22 at 1:35 pm
Adbo, I can't look at your file now, please update your question and explain what you want to happen, what you tried, and what is not working. Also, what are you using a dictionary object? Why not just put everything directly into the combobox's or into arrays? Maybe I am not understanding what you're trying to do exactly.
don (rep: 1989) Jun 9, '22 at 11:10 am
Abdo. As Don says, using an array will be simpler (and faster) but your question lacks key details (which might explain why you used dictionaries) so you need to answer Don's points please..

E.g . do your ComboBoxes get completed from left to right (as we'd expect in the West)?

Is every combination of BATCH/ PR.NO. /TY.TT / MM.NN unique? Or could a BATCH (say BBT-QA4) have several options for PR.NO. (say FZ-100, FZ-101 and FZ-102) etc?
John_Ru (rep: 6102) Jun 10, '22 at 9:33 am
Hi don, John
actually  I  do   my  best   to  explain what  I  try   to  do  . I  knew    from  the  beginning  my  question  is  so  complicated . the  userform  contains  multiple  rows , each row  contains( 4 comboboxes & 1  textbox)  so  when  see  the  lables  for  all of comboboxes   should  match  with  the  headers in sheet and  when  select  item from combobox1 automatically fill combobox2 and  when select  item from combobox2 automatically fill combobox3  and  when select item from  combobox3 automatically fill combobox4  and  when  select  item  from combobox4  will fill  the  textbox 19  . so  the  columns 1,2,3,4,5  on userform link  with columns  B,C,D,E,F  every  combobox  depends  on each  other  of  them  when  select one  of  them  and  depends  on adjacent  cell  when  match  the  columns  inside   sheet for  each    combobox  when selected item for combobox separately .
as  to  John' s questions 
E.g . do your ComboBoxes get completed from left to right (as we'd expect in the West)?
is  it  problem ? actually  from   right to  left
Is every combination of BATCH/ PR.NO. /TY.TT / MM.NN unique? Or could a BATCH (say BBT-QA4) have several options for PR.NO. (say FZ-100, FZ-101 and FZ-102) etc? absolutely  not  because   Each item has a different ID  as  in column B 
Abdo M (rep: 16) Jun 10, '22 at 12:08 pm
Thanks for the clarifications Abdo but they lead to further questions in my mind...

You give the example "select  item from combobox1 automatically fill combobox2" and also that the boxes are completed "from right to  left" (yet combobox1 is to the LEFT of combobox2).  

If they are indeed completed "from right to  left" then it feels like you would set ComboBox4 (MM.NN) to "GR" for and expect ComboBox3 (TY.TT) to offer to FFZ-105, FF-Z106 and FZ100. Picking FZ100 would give BBT-QA8 for CB2 and REF-FERR8 for CB3 plus 130.00 for TextBox18 (QTY). Or am I more confused now?
John_Ru (rep: 6102) Jun 10, '22 at 1:15 pm
 am I more confused now?
my  apologies  John !!! I seem  to  misunderstood  . in  my  mind  another  thing  seems  stupid ! 
as  you  siad  and  based  on the  file  left  to  right    based  on  column B  and  start from combobox1  .
Abdo M (rep: 16) Jun 10, '22 at 2:59 pm
Thanks, that's clearer now. So (assuming this is only test data), if you make a selection in one box (say ComboBox1), can there be more than one option (from your real data table) for the box to its right (e.g. ComboBox2)?

Also, will the userform used to create a report (e.g. a set or tasks or orders for someone to deal with)? 
John_Ru (rep: 6102) Jun 10, '22 at 5:14 pm
 if you make a selection in one box (say ComboBox1), can there be more than one option (from your real data table) for the box to its 
this case is rare but sometimes this possibility can occur.
Also, will the userform used to create a report (e.g. a set or tasks or orders for someone to deal with)? 
I'm not  sure  if  I  understand  this  sentence . but  to  be  clear  after  populate all of  the  items in  all of  the  selected comboboxes and  textboxes    will copy  to  another  sheet  .    
Abdo M (rep: 16) Jun 11, '22 at 3:33 am
Thanks again.

1) If column B is a unique key (no repeats) ,  it will be easy to populate ComboBoxes to the right of a selection.

2) I was asking the purposes of the form. When items on the Userform are copied to another sheet, should they be removed from the source sheet? 
John_Ru (rep: 6102) Jun 11, '22 at 4:08 am
If column B is a unique key (no repeats) ,  it will be easy to populate ComboBoxes to the right of a selection.
actually  yes  you'r  right      when I  said  somtimes   occures  maybe   the  user   mistake  when  enter  data ( it  doesn't  matter  and  ignore  this  point ) I  can add some lines  to  prevent  duplcate  entering in column B. 
as  you  said  no  repeat keys .
should they be removed from the source sheet? 
no .  I  need  source  sheet  . the  second  sheet  will be  invoice  depends on source  sheet . the  source  sheet  is  entering  basic data  and  will increase ever day .  
Abdo M (rep: 16) Jun 11, '22 at 4:58 am
Add to Discussion

Answers

0
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.

Discuss

Discussion

P.s. the use of comboboxes (or items other than Batch) doesn't make much sense in the context of my Answer but I've populated the lists anyway (and "picked" the only entry returned from the dictionary array)
John_Ru (rep: 6102) Jun 11, '22 at 10:52 am
many  thanks  for  your  time & help 
Abdo M (rep: 16) Jun 11, '22 at 2:53 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login