I can't get this dependent multiple combo box menu to work.........

IziJp

New Member
Joined
Dec 9, 2020
Messages
1
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
I want to use this combobox menu dependently to access 10 different category menu with its sub-category, item, sub-item etc.
The vab code isn't displaying the menus with ID 10, 100, 101 etc, please how do I fix this? I have been on it for a week now, but no luck.
PLEASE HELP!!!



VBA Code:
Option Explicit

Dim id_line As Integer           ' 加工ライン名のシート
Dim id_section As Integer        ' メンテナス項目のシート
Dim id_koukan As Integer         '交換ヶ所のシート
Dim id_subsection1 As Integer    '交換部品その①のシート
Dim id_subsection2 As Integer    '交換部品その②のシート


Private Sub cbo_Kakoumei_Change()
Dim a As Integer
Dim b As Integer

cbo_Section.Clear

With Sheet2 ' メンテナス項目のシート
        
        id_line = cbo_Kakoumei.ListIndex + 1
        
    b = .Cells(1, 1).End(xlDown).Row

    For a = 2 To b
        If Mid(.Cells(a, 1), 1, 1) = id_line Then
            cbo_Section.AddItem (.Cells(a, 2))
        End If
    Next

End With

End Sub

Private Sub cbo_Section_Change()
Dim a As Integer
Dim b As Integer

cbo_KoukanKasho.Clear

With Sheet3 ' 交換ヶ所のシート
        
        id_section = cbo_Section.ListIndex + 1
        
    b = .Cells(1, 1).End(xlDown).Row

    For a = 2 To b
        If Mid(.Cells(a, 1), 1, 1) = id_line And _
           Mid(.Cells(a, 1), 2, 1) = id_section Then
          
            cbo_KoukanKasho.AddItem (.Cells(a, 2))
            
        End If
    Next

End With

End Sub

Private Sub cbo_KoukanKasho_Change()
Dim a As Integer
Dim b As Integer

cbo_Koukanbuhin1.Clear

With Sheet4 '交換部品その①のシート

        id_koukan = cbo_KoukanKasho.ListIndex + 2
        

    b = .Cells(1, 1).End(xlDown).Row

    For a = 2 To b
        If Mid(.Cells(a, 1), 1, 1) = id_line And _
           Mid(.Cells(a, 1), 2, 1) = id_section And _
           Mid(.Cells(a, 1), 3, 1) = id_koukan Then
          
            cbo_Koukanbuhin1.AddItem (.Cells(a, 2))
            
        End If
    Next

End With
End Sub

Private Sub cbo_Koukanbuhin1_Change()
Dim a As Integer
Dim b As Integer

cbo_Koukanbuhin2.Clear

With Sheet5 '交換部品その②のシート

        id_subsection1 = cbo_Koukanbuhin1.ListIndex + 2
        

    b = .Cells(1, 1).End(xlDown).Row

    For a = 2 To b
        If Mid(.Cells(a, 1), 1, 1) = id_line And _
           Mid(.Cells(a, 1), 2, 1) = id_section And _
           Mid(.Cells(a, 1), 3, 1) = id_koukan And _
           Mid(.Cells(a, 1), 4, 1) = id_subsection1 Then
            
           cbo_Koukanbuhin2.AddItem (.Cells(a, 2))
          
        End If
    Next

End With
End Sub

Private Sub cbo_Koukanbuhin2_Change()
Dim a As Integer
Dim b As Integer

With Sheet5 '交換部品その②のシート

        id_subsection2 = cbo_Koukanbuhin2.ListIndex + 2
        

    b = .Cells(1, 1).End(xlDown).Row

    For a = 2 To b
        If Mid(.Cells(a, 1), 1, 1) = id_line And _
           Mid(.Cells(a, 1), 2, 1) = id_section And _
           Mid(.Cells(a, 1), 3, 1) = id_koukan And _
           Mid(.Cells(a, 1), 4, 1) = id_subsection1 And _
           Mid(.Cells(a, 1), 5, 1) = id_subsection2 Then

            
        End If
    Next

End With
End Sub


Private Sub UserForm_Initialize()
Dim a As Integer
Dim b As Integer

With Sheet1 '加工ライン名

b = .Cells(1, 1).End(xlDown).Row

    For a = 2 To b
        If .Cells(a, 1) <> "" Then
            cbo_Kakoumei.AddItem (.Cells(a, 2))
        End If
    Next

End With
End Sub
 

Attachments

  • ComboBox.jpg
    ComboBox.jpg
    89.2 KB · Views: 12

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Forum statistics

Threads
1,223,898
Messages
6,175,272
Members
452,628
Latest member
dd2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top