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

 

[ipbr21054]

All working
No matter how many times i looked i didnt see an extra letter

Thanks for your time

 

[dmt32]

Model Table is different to Model Tables & as said in my post, you need to check that the names match otherwise you will get out of range error - glad you managed to resolve

Dave

 

[ipbr21054]

This making me look stupied.
Because its doing it the same again.

I copied the working code from test file to word doc.
I then opened original files & pasted code.
Closed & saved

The i wrote down the sheets names & renamed on original file

Its doing the same but on test file it works.

Ive done it 4 times now

 

[ipbr21054]

Right ive had to copy the sheets from the test file to my original file.
What i now see is KAWASAKI & YAMAHA only show the error.

But what is stange is the following

KAWASKAI fails & i debug it to see this line in yellow

     'models
     With Me.ComboBox4
        .RowSource = ""
        .List = tbl(2).DataBodyRange.Value
     End With

YAMAHA fails & i debug it to see this line in yellow

    'parts
     With Me.ComboBox8
        .RowSource = ""
        .List = tbl(1).DataBodyRange.Value
     End With

 

[dmt32]

You know code works ok so error suggests that in transfer, need to Check the table & sheet names all correct

Dave

 

[ipbr21054]

I have & correct

Here is the code

Private Sub ComboBox3_Change()
    Dim wsPartsTables          As Worksheet
    Dim wsModelTables          As Worksheet
    Dim tbl(1 To 2)            As ListObject
    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 Table")
  
     'set object variable to combobox model tables worksheet
    Set wsModelTables = ThisWorkbook.Worksheets("Model Table")
  
    Set tbl(1) = wsPartsTables.ListObjects(whichtable & "Table")
  
    Set tbl(2) = wsModelTables.ListObjects(whichtable & "Model")
  
    'parts
     With Me.ComboBox8
        .RowSource = ""
        .List = tbl(1).DataBodyRange.Value
     End With
   
     'models
     With Me.ComboBox4
        .RowSource = ""
        .List = tbl(2).DataBodyRange.Value
     End With
  
End Sub

Supplied screen shot of table names
I copied from test file so no need to edit them,working in test file but not in original.
Same goes for cod just copied from one to the other

 

[ipbr21054]

This code here

    'parts
     With Me.ComboBox8
        .RowSource = ""
        .List = tbl(1).DataBodyRange.Value
     End With
   
     'models
     With Me.ComboBox4
        .RowSource = ""
        .List = tbl(2).DataBodyRange.Value
     End With

Top code shows 1 & Bottom code shows2
HONDA works
KAWASAKI fails
SUZUKI works
YAMAHA fails

NOW SWITCH NUMBERS
Top code shows 2 & Bottom code shows 1 I know part numbers will be show in model combobox but testing it
HONDA works
KAWASAKI fails
SUZUKI works
YAMAHA fails

So either way the code doesnt see KAWASAKI or YAMAHA

 

[dmt32]

check each of the names in your tables

Dave

 

[ipbr21054]

I have but dont see any issues
Do you see an issue here ?

 

[ipbr21054]

Hi,
I just looked at the text file using your code & it does the same>

Please see if KaWASAKI & YAMAHA also fail for you