Userform to show only specific data in combobox dependant on other combobox selection

[ipbr21054]

Hi,
I have a userform with various comboboxes.
Ther 2 comboboxes in question are as follows.

ComboBox3 of which is MAKE comprising of "HONDA" "KAWASAKI" "YAMAHA" & are the values in drop down list
This is Table 1

ComboBox8 of which is ORIGINAL PART NUMBERS comprising of ALL part number for all the makes mentioned above currently say 20 of them
Currently this is Table 6

So example of what i would like to do.
If HONDA is selected in ComboBox3 then only show the honda part numbers in ComboBox8

So i assume that i need new tables for each make so i have now done that.
HONDA is Table 9
KAWASAKI is Table 10
YAMAHA is Table 11

Later i will be adding more but for now can you advise how i write the code for ComboBox8

Example
If HONDA is selected only show Table 9 results
ELSE
If KAWASAKI is selected only show Table 10 result
ELSE
If YAMAHA is selected only show Table 11 result


Many thanks

 

[dmt32]

Pictures do not really help - as said, you know code works so clearly something is different in a transfer to your main workbook
- Just need to keep checking each table & worksheet name to ensure all match & hopefully, you will find the culprit causing the error

Dave

 

[ipbr21054]

Does KAWASAKI & YAMAHA also fail for you
The photos were to show the name / spelling etc of tabs & in the code of which are the same.

 

[ipbr21054]

There are only 2 works sheets
Parts Table & Model Table
I originally had HONDA KAWASAKI SUZUKI YAMAHA
But i now ive added to it so HONDA KAWASAKI SUZUKI YAMAHA APRILLIA PIAGGIO

I run the code again & only HANDA & SUZUKI work

I cant make that many spelling mistakes.
Please advise if ALL work for you or does KAWASAKI & YAMAHA also fail
Thanks,

 

[dmt32]

I was expecting in your main workbook to have more than one entry for each table - with a single entry, the value returned is not an array that the List property requires

See if this update resolves

Private Sub ComboBox3_Change()
    Dim wsPartsTables          As Worksheet
    Dim wsModelTables          As Worksheet
    Dim tbl(1 To 2)            As ListObject
    Dim arr(1 To 2)            As Variant
    Dim whichtable             As String
     
    'make
    whichtable = Me.ComboBox3.Value
    If Len(whichtable) = 0 Then Exit Sub
 
      'set object variable to combobox parts tables worksheet
    Set wsPartsTables = ThisWorkbook.Worksheets("Parts Tables")
 
     'set object variable to combobox model tables worksheet
    Set wsModelTables = ThisWorkbook.Worksheets("Model Tables")
 
    Set tbl(1) = wsPartsTables.ListObjects(whichtable & "Table")
    arr(1) = tbl(1).DataBodyRange.Value: If Not IsArray(arr(1)) Then arr(1) = Array(arr(1))
 
    Set tbl(2) = wsModelTables.ListObjects(whichtable & "Model")
    arr(2) = tbl(2).DataBodyRange.Value: If Not IsArray(arr(2)) Then arr(1) = Array(arr(2))
 
    'parts
     With Me.ComboBox8
        .RowSource = ""
        .List = arr(1)
     End With
  
     'models
     With Me.ComboBox4
        .RowSource = ""
        .List = arr(2)
     End With
 
End Sub

 

[ipbr21054]

Now that did the trick.

Many thanks for following this up

 

[dmt32]

Now that did the trick.

Many thanks for following this up

welcome glad resolved

Dave