Multi-level dependant lists ComboBoxes

0

hi experts  

I  have   userform  contain  multiple  comboboxes    .  the  code   just  apply  in three  comboboxes   ,  but    I  want  apply  for  each  three combobox  . so  the  comboboxes 1,2,3    linked  with COL B,C,D   when  select   combobox1  based on COL B . it  will populate  in combobx2 based on COL C  and   populate  in combobox3  based on COL D.   

 it  should  apply   the  same  thing  for  each three combobox(4,5,6)    combobox(7,8,9)  combobox (10,11,12) 

thanks   fo  anybody  provide  the assistance 

Answer
Discuss

Discussion

Hi Tubrak,
Your code fillls Comboboxes2 and 3 with single items. Why don't you use TextBoxes for that? Also, you want 11 ComboBoxes filled but your worksheet has only 5 columns. Where are the data you want in the other ComboBoxes or TextBoxes?
Variatus (rep: 4864) Aug 4, '21 at 8:33 pm
about  the textbox  you're right  
but this is the step1 for my project .I will issue another question .
I excpected  you  say that  about filling the comboboxes  and this is what I look for how fill like comboboxes 1,2,3  linked with COLS  B,C,D  . my aim when fill data for each three  comboboxes   copies  to sheet  at once instead  of depends  just three comboboxes   every time . It will take  more time  . so it should  link like comboboxes 1,2,3  for the res of comboboxes  with COLS B,C,D .
tubrak (rep: 4) Aug 4, '21 at 11:49 pm
Add to Discussion

Answers

0

Hello Tubrak,

In the attached workbook I had added the following code in the renamed module TBX_5675.

Option Explicit

Sub ShowUserForm()
    ' 307

    Dim FrmNoName   As New fmNoName
    Dim DataRng     As Range        ' data range of "Inventory" tab
    Dim CbxNames()  As String       ' list of Cbx controls
    Dim i           As Long         ' loop counter

    With Worksheets("Inventory")
        ' there must be 1 column for each Cbx in CbxNames
        Set DataRng = .Range(.Cells(2, "B"), .Cells(.Rows.Count, "D").End(xlUp))
    End With

    CbxNames = Split("CbxCom CbxTT CbxPR")
    With FrmNoName
        ' the form's initialize event is triggered here
        ' all controls are now available here
        For i = 0 To UBound(CbxNames)
            With .Controls(CbxNames(i))
                .List = mySort(DataRng.Columns(i + 1))
                .ListIndex = 0      ' this will trigger the control's Change event
            End With
        Next i
        .Show

        ' when the OK button on the form is clicked
        ' code resumes here with the form hidden but all
        ' controlls still accessible.
        Dim Txt     As String
        For i = 0 To UBound(CbxNames)
            CbxNames(i) = CbxNames(i) & vbTab & .Controls(CbxNames(i)).Value
        Next i
        MsgBox Join(CbxNames, vbCr), vbInformation, "Result"
    End With
    Unload FrmNoName
    Set FrmNoName = Nothing
End Sub

Function mySort(Arr As Variant) As Variant
    ' 307

    Dim R           As Long             ' loop counter: Arr() row
    Dim f           As Long             ' loop counter: Temp
    Dim Tmp         As String

    Arr = Application.Transpose(Arr)
    For R = LBound(Arr) To UBound(Arr) - 1
        For f = R + 1 To UBound(Arr)
            If Arr(R) > Arr(f) Then
                Tmp = Arr(R)
                Arr(R) = Arr(f)
                Arr(f) = Tmp
            End If
        Next
    Next
    mySort = Arr
End Function

I have also renamed your user form and three of its combo boxes and added a command button which I named CmdOK. It's important that you don't unload the form in its own code because if you hide it control is resumed by the calling procedure and you can extract data from the form before destroying the form there.

Two things. One, I really don't understand what you want to load to the other 9 combo boxes. However, I have shown the way to do it and you should have no further problem replicating the same method.

Two, I'm reluctant to help you because I spent half my time doing work you could have done equally well if not better. Comprehensive and meaningful naming of procedures and variables is very important. It saves everybody's time, including and especially your own. Option Explicit at the top of each module isn't a luxury but a little extra which will save you hours and hours of work. Proper formatting of the code makes it more readable and therefore easier - faster - to understand and modify. Formatting includes proper indenting and avoiding the merge of several code lines into one with commas (in the case of Dim statements) and colons. It may be my own inability but it is one that is shared by all who do programming regularly: I can't work with code that looks like this:-

Sub J3v16()
Dim Data, Chk, temp, Dict As Object, ws As Worksheet, Str As String, i As Long, x As Long, xx As Long: xx = 3

If you want my help in the future please clear up the mess before you post your code on this forum. I have cleaned out the mySort function for you. You can use it of an example of the work you should do. You may not be able to do it perfectly but you can do it in a way that displays your effort rather than the lack of it.

Discuss

Discussion

sorry  about   this  code Sub J3v16()  it shouldn't  existed  in  my  file    and  despite  of  I  try explaining   what    I    try  to  do  that  but  you  don't  understand  me  . sorry  I  say   this  is  not what  I  want . my  code  works  perfectly  for  combobox1,2,3   each  combobox  depends on each  other   but  you  cancelled  this  and       . the  request  just  how  adapt  the  code    to  apply  rest  of combobox  .  I  have  ever  said  should link  with  COL B,C,D   the  same  thing  what  occurs  with combobox1,2,3   should  occurs  with combobox 4,5,6        and  combobox 7,8,9  and  combobox  10,11,12   when  select item from combobox4 it will populate  in combobox5 and  when select item from combobox5 it will populate in combobox 6    the  same  thing with (combobox7,8,9 ) and (combobox10,11,12). hoenstly  I  do  my  best  to explain  what my  request but unfortunately  you  don't  understand me . anyway  thanks  for  your time 
tubrak (rep: 4) Aug 5, '21 at 6:55 am
Add to Discussion


Answer the Question

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