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

fill dynamic in combobox across multiple sheets without duplicated

0

hi, experts

i  try  filling  items on three combobox   dynamically across multiple  sheets   based on  column b,c,d   but  the  problem    shows   many  duplicated values    of  course   there   are  many  items  repeated   in  some  sheets   but  i  do  not  want  do  that   and   another  problem   it   shows  many  duplicate   every  item  and   combobox3    not  show   items  based  on  column  d     

would  anybode   find  out  what's  the  problem  and  how  fix  it  

Private Sub UserForm_Initialize()
Dim Ws As Worksheet, rCell, rcell1, rcell2 As Range
 For Each Ws In ActiveWorkbook.Worksheets
 Select Case UCase(Ws.Name)
 Case "IMPORT", "EXPORT", "RETURNS", "WHAREHOUSE"
 With Ws
 For Each rCell In .Range("b2", .Cells(Rows.Count, 2).End(xlUp))
 For Each rcell1 In .Range("c2", .Cells(Rows.Count, 3).End(xlUp))
 For Each rcell2 In .Range("d2", .Cells(Rows.Count, 4).End(xlUp))
 ComboBox1.AddItem rCell
 ComboBox2.AddItem rcell1
 ComboBox3.AddItem rcell1

 Next
 Next
 Next

 End With
End Select
Next Ws

End Sub
Answer
Discuss

Discussion

There must be one master list or relevant list. Maybe you can't export what you didn't import, and certainly nothing can be returned that wasn't sold. Or, perhaps, your user form only wants the imported, exported or returned items.For any of these reasons it should be unnecessary to process all 3 sheets. Please clarify your intention.
For another, your own effort is lacking. Please run your code and find out where it breaks. That's the point we can help you with.
Variatus (rep: 4889) Jan 20, '21 at 6:57 pm
HI Variatus ! ok I edieted  my  file   this  is  what  I  want    my  form  contanis  multiple  textboxes   the  aim  of  that  is  populate  the  values    based on  select  from comboboxes  firs  I  want  show  three comboboxes  for  all items  from  multiple  sheets  without  any  duplicated  and  if  there  is  many  items are repeated  whether in  the  same  sheet  or  other sheets   , the comboboxes should   ignore  them but  when  i  want  populate  in textbox  ,    it  must   search  in  all  sheets   and  if  there  is  existed  then  poulate in texbox relating  the  sheet  as  is  the  labels   names  based on  sheet names  with  considering  if    there  is   item  are  repeated   then  merge  and  sum  the  values  then  populate  the  textbox  
this  is  th  aim  all  what  i  look  for
leopard (rep: 88) Jan 21, '21 at 1:36 am
Add to Discussion

Answers

0
Selected Answer

Leopard

Please try the attached file (full code below) which will populate the Code, Brand and Model comboboxes without duplicates. Note that I've corrected your sheet and combobox to use the correct spelling, Warehouse.

I've set up three arrays at the start e.g. CodeArr() . These get populated (and resized) with every matching item found in each of the sheets (using separate counters) as in this example (with extra comments added):

        For Each rCell In .Range("b2", .Cells(Rows.Count, 2).End(xlUp))
            i = i + 1 'increment a counter (and keep for next sheet)
            ReDim Preserve CodeArr(i) 'make the array bigger, preserving values (leaving entry 0 as blank)
            CodeArr(i) = rCell.Value 'make the ith value of the array the cell value; you should collecting other array data here too
        Next rCell 'Loop within the sheet

When the For Next (Ws)  loop is done, they're will be 3 arrays, containing vlaues from all three sheets.

I then call a function ArrayRemoveDups (from another forum VBA – Remove Duplicates From Array) to remove the duplicates and populate the combox box with the (unduplicated) values from the clean array "output" e.g.:

Output = ArrayRemoveDups(CodeArr) ' call function to remove duplicates
For i = 0 To UBound(Output) 'populate combobox list
    ComboBox1.AddItem Output(i)
Next i

.

Put it all together you get this code (and you'll see the ArrayRemoveDups function code in the file):

Private Sub UserForm_Initialize()
Dim Ws As Worksheet, rCell As Range, rcell1 As Range, rcell2 As Range
Dim CodeArr() As Variant, BrandArr() As Variant, ModelArr() As Variant

For Each Ws In ActiveWorkbook.Worksheets
     With Ws
        For Each rCell In .Range("b2", .Cells(Rows.Count, 2).End(xlUp))
            i = i + 1
            ReDim Preserve CodeArr(i)
            CodeArr(i) = rCell.Value 'collect other array data here?
        Next rCell

        For Each rcell1 In .Range("c2", .Cells(Rows.Count, 3).End(xlUp))
            m = m + 1
            ReDim Preserve BrandArr(i)
            BrandArr(m) = rcell1.Value 'collect other array data here?
        Next rcell1

        For Each rcell2 In .Range("d2", .Cells(Rows.Count, 4).End(xlUp))
            n = n + 1
            ReDim Preserve ModelArr(i)
            ModelArr(n) = rcell2.Value 'collect other array data here?
        Next rcell2

     End With
Next Ws

Output = ArrayRemoveDups(CodeArr) ' call function to remove duplicates
For i = 0 To UBound(Output) 'populate combobox list
    ComboBox1.AddItem Output(i)
Next i

Output = ArrayRemoveDups(BrandArr)
For i = 0 To UBound(Output)
    ComboBox2.AddItem Output(i)
Next i

Output = ArrayRemoveDups(ModelArr)
For i = 0 To UBound(Output)
    ComboBox3.AddItem Output(i)
Next i

End Sub

Incidentally your original macro has an unnecessary Select Case statement (could be removed without issue) and incorrectly nested loops (which caused many more duplicates than necessary). Also note that if you DIM rcell (without "As Range"), it will be the default type (Variant); only rcell2 is correctly scoped As Range in your DIM statement.

Hope the code above helps- I leave you to think how you collect/present the other data.

Discuss

Discussion

P.s. you need to think how you will collect and display the other data (I added the comment 'collect other array data here? where you might do it)
John_Ru (rep: 6102) Jan 21, '21 at 6:52 am
well done  !  john    first  of  all  I  considering  you've  solved  my  post    the  second  I  try  understand  your  code   ,may  you  give  some  time ? the  third  I try  to  add  some  lines   to  your  codes   to  populate   and  sum  values  in  multiple  textboxes ,  if I  failed  I hope  following  my  post  and  guide  me  the  correction  way  finally  , do  me  favor,please , if  you  have  any  source  to  explain  the  array   please  provide  me, I  would   understand  how  use  it    always  hear   from experts  prefers  using  array  like  my  post   because of it  's   fast in  run  code   
leopard (rep: 88) Jan 21, '21 at 7:27 am
Leopard.

I've said how the code works basically. In more detail, we set up a variable named with () to make it an array, e.g. CodeArr(). At that stage, we don't know how big it will be and there's one position (index 0) and it's empty. In the first code extract above, counter i is set to 1 and we resize the array then make the value in position 1 the same as the 1st rcell. It loops around then puts a value in position 2, 3 etc.; first looping within the first sheet then within the other sheets.

Once the For Each ws.../Next Ws loop is finished, each array contains all values from all the sheets but many duplicates. We then call the function to remove those and only then populate each ComboBox.

What you do next is up to you (I don't know what you're attempting to show here). If you have many items, it might be helpful to sort the array I call output before writing the ComboBoxes. Seems to me you will need to write subs for the Import textbox (say) to populate that based on the selections made for Code, Brand or Model (which is probably uniquely linked to Code).

Note that I used three "1-D" arrays but a single array can have several dimensions e.g. to collect all your data in one sweep, then you might extract a dimension for removing duplicates/sorting to fill Comboboxes) or extracting full/part records to populate textboxes like Import. I think that would be too complicated for you (and I don't have unlimited time!).
John_Ru (rep: 6102) Jan 21, '21 at 9:05 am
@john   you  misundrstood  me  when  Iask  you  to  guide  me  about  the  array  I  don't  mean  in  your  code  I  see   your   comments  into  the  code  every  line   , what  I  meant   to  learn  about  basics  the  array   if  you  have  any  source   to  start  learning  ,  I  would  appreciate  you 
leopard (rep: 88) Jan 21, '21 at 10:43 am
Leopard. I learned from a book (so can't sent you that!) but you will find 2 or 3 tutorials from Don on this site. Search the Internet for "excel vba" arrays and you should find many sources of information, e. g. Arrays
John_Ru (rep: 6102) Jan 21, '21 at 11:32 am
Add to Discussion


Answer the Question

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