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



## ipbr21054 (Saturday at 12:01 PM)

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


----------



## Micron (Saturday at 12:04 PM)

I know how to do this in Access, but not Excel. What you want is called cascading comboboxes. One example





						Cascading Drop Down Lists in Excel — Excel Dashboards VBA
					

Creating cascading dynamic combo boxes (combo boxes which are linked) in Excel is a very handy tool. It can be done with or without VBA.




					www.thesmallman.com


----------



## ipbr21054 (Saturday at 12:12 PM)

Thanks,but i think it needs to be done something like this


```
Private Sub ComboBox8_Change()
If ComboBox3.Value = "HONDA" Then
Table9.Show
Else
If ComboBox3.Value = "KAWASAKI" Then
Table10.Show
Else
If ComboBox3.Value = "YAMAHA" Then
Table11.Show
eN DIF
End Sub
```


----------



## ipbr21054 (Saturday at 12:40 PM)

I think you are correct sorry.
Just need to see how to do it

Thanks


----------



## dmt32 (Saturday at 2:02 PM)

Hi,
assuming that your data is listed in Tables (not ranges) all on same sheet like example below, then suggest that you first give each table a *meaningful name* that reflects the values in your ComboBox3 list with the added suffix *Table *mainly to identify them as tables.

Combobox3 Values
Honda
Kawasaki
Yamaha

The Table Names would be

*HondaTable
KawasakiTable
YamahaTable*

and so on

Place this code in your userforms code page


```
Private Sub ComboBox3_Change()
    Dim wsListTables  As Worksheet
    Dim tbl           As ListObject
    Dim whichtable    As String
   
    whichtable = Me.ComboBox3.Value
    If Len(whichtable) = 0 Then Exit Sub
   
      'set object variable to combobox parts tables worksheet
    Set wsListTables = ThisWorkbook.Worksheets("*Parts Tables*")
   
    Set tbl = wsListTables.ListObjects(whichtable & "table")
   
    Me.ComboBox8.List = tbl.DataBodyRange.Value
   
End Sub
```

Change the name of parts tables worksheet as required

Dave




07-01-2023.xlsABCDE1HondaKAWASAKIYAMAHA2HON Part1KW Part1YAM Part13HON Part2KW Part2YAM Part24HON Part3KW Part3YAM Part35HON Part4KW Part4YAM Part46HON Part6KW Part6YAM Part67HON Part5KW Part5YAM Part58HON Part7KW Part7YAM Part7Parts Tables


----------



## ipbr21054 (Saturday at 2:17 PM)

Hi,
I have this now in place.
The worksheet where the tables are is called DATABASE
I see now values in CB3 or CB8

Previously i had RowSource in CB properties, was i right to remove that now ?


```
Private Sub ComboBox3_Change()
    Dim wsListTables  As Worksheet
    Dim tbl           As ListObject
    Dim whichtable    As String
    
    whichtable = Me.ComboBox3.Value
    If Len(whichtable) = 0 Then Exit Sub
    
      'set object variable to combobox parts tables worksheet
    Set wsListTables = ThisWorkbook.Worksheets("DATABASE")
    
    Set tbl = wsListTables.ListObjects(whichtable & "table")
    
    Me.ComboBox8.List = tbl.DataBodyRange.Value

End Sub
```


----------



## ipbr21054 (Saturday at 2:23 PM)

Putting row source back in propertis i now see it working.

Thanks


----------



## Micron (Saturday at 2:23 PM)

I have little grasp of tables in Excel but I won't say that about a true database. So I think I'll bow out as I'll never use whatever I could learn about that.
In a db, this would be 1 tbl for Make, 1 for parts, and a junction table for parts/make. Hope you get a solution.


----------



## ipbr21054 (Saturday at 6:14 PM)

dmt32 said:


> Hi,
> assuming that your data is listed in Tables (not ranges) all on same sheet like example below, then suggest that you first give each table a *meaningful name* that reflects the values in your ComboBox3 list with the added suffix *Table *mainly to identify them as tables.
> 
> Combobox3 Values
> ...




This was very good & wondering if you could go one step further please. 

Using the same Combobox3 values can we ALSO populate Combobox4 with MODELS
It makes sense to now not only load part numbers but also models. 
The table will still be on the same worksheet. 
Tables will be names like so,
HondaModels
KawasakiModels
YamahaModels

Thanks.


----------



## ipbr21054 (Saturday at 6:38 PM)

Would it be like this ?


```
Private Sub ComboBox3_Change()
    Dim wsListTables  As Worksheet
    Dim tbl           As ListObject
    Dim whichtable    As String
 
    whichtable = Me.ComboBox3.Value
    If Len(whichtable) = 0 Then Exit Sub
 
    Set wsListTables = ThisWorkbook.Worksheets("DATABASE")
 
    Set tbl = wsListTables.ListObjects(whichtable & "table")
 
    Me.ComboBox8.List = tbl.DataBodyRange.Value
    Me.ComboBox4.List = tbl.DataBodyRange.Value
```


----------



## ipbr21054 (Saturday at 12:01 PM)

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 (Sunday at 4:19 AM)

ipbr21054 said:


> This was very good & wondering if you could go one step further please.
> 
> Using the same Combobox3 values can we ALSO populate Combobox4 with MODELS
> 
> ...



Yes you can - just create tables similar to my example using your suggested naming convention & name the worksheet appropriately (Model Tables)

the use updated 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 Tables*")
  
     'set object variable to combobox model tables worksheet
    Set wsModelTables = ThisWorkbook.Worksheets("*Model Tables*")
  
    Set tbl(1) = wsPartsTables.ListObjects(whichtable & "table")
  
    Set tbl(2) = wsModelTables.ListObjects(whichtable & "Models")
  
    '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
```

Personally, I would name the parts table worksheet "Parts Tables" as it is more representative as to it purpose than "Database" - I would also suggest that you consider updating your combobox names from their default names to something more meaningful like

*cboMake
cboModel
cboParts*

but your choice - just update combobox & worksheet names as appropriate.

Dave


----------



## ipbr21054 (Sunday at 4:58 AM)

I have applied the code & i see Could not set the list property,invalid property array
I debug & see the line of code below in red shown.

Im using ComboBox 4 & 8
Both tables are on worksheet DATABASE
Tables are like so HondaTable & HondaModel


```
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("DATABASE")
  
     'set object variable to combobox model tables worksheet
    Set wsModelTables = ThisWorkbook.Worksheets("DATABASE")
  
    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
```


----------



## dmt32 (Sunday at 5:23 AM)

Post copy of your tables worksheet using MrExcel Addin XL2BB - Excel Range to BBCode

Dave


----------



## ipbr21054 (Sunday at 5:33 AM)

See here

TEST.xlsmKLMNOPQRSTUVWXYZ1HONDA PNKAWASAKI PNSUZUKI TABLEYAMAHA PNPIAGGIO PNAPRILIA PNHONDA MKAWASAKI MSUZUKI MYAMAHA MPIAGGIO MAPRILLIA M235121-MAT-E0027008061537146-26D10-0005SL-82511-08-00CB 1000ZZR 1400GSXR 1000 RRMT09EF335121-MBW-60137146-33E00-000CB 1300GSXR 600R1435121-MCA-811CB 500 FST 1300XJR 1300535121-MCJ-D00CB 500 XRF 900XVS 1300635121-MEH-631CB 600 F735121-MFJ-D00CB 600 N835121-MFL-641CB 650 F935121-MFN-D01CB 900 F1035121-MGH-641CBF 10001135121-MGP-D01CBF 6001235121-MGP-D63CBR 1000    1335121-MGS-A31CBR 1000 RR1435121-MJE-D02CBR 1100 XX1535121-MJG-641CBR 600 F1672147-MCA-305CBR 600 R1772147-MCA-315CBR 600 RR1872147-MCA-671CBR 650 F1972147-MJK-305CBR 650 R2072147-MKJ-326CBR 900 RR21CBR 95422GOLD WING23NC 700 S24NC 700 X25NC 75026NC 750 X27NT 65028NT 70029SILVER WING30SH 300 A31ST 130032VALKYRIE33VFR 120034VFR 80035VTR 100036X1137XL 65038XL 70039AFRICA TWINDATABASE


----------



## dmt32 (Sunday at 5:55 AM)

sheet looks like you are using a ranges

Can I suggest that you follow my suggested guidance - Insert a new sheet & name it Model Tables

then create your tables in following format

07-01-2023.xlsABCDE1HondaKAWASAKIYAMAHA2HON ModelKW ModelYAM Model3HON ModelKW ModelYAM Model4HON ModelKW ModelYAM Model5HON ModelKW ModelYAM Model6HON ModelKW ModelYAM Model7HON ModelKW ModelYAM Model8HON ModelKW ModelYAM ModelModel Tables

Tables should be named as per you naming suggestion

HondaModels
KawasakiModels
YamahaModels

Dave


----------



## ipbr21054 (Sunday at 6:17 AM)

Well i obviously cant follow this now for some reason.

I have now created two new worksheets.
MODEL TABLES & PART NUMBER TABLES.

My code if like so but i now keep getting subscript out of range.
What am i doing wrong please


```
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("MODEL TABLES")
  
     'set object variable to combobox model tables worksheet
    Set wsModelTables = ThisWorkbook.Worksheets("PART NUMBER TABLES")
  
    Set tbl(1) = wsPartsTables.ListObjects(whichtable & "table")
  
    Set tbl(2) = wsModelTables.ListObjects(whichtable & "table")
  
    '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
```


----------



## dmt32 (Sunday at 6:28 AM)

ipbr21054 said:


> Well i obviously cant follow this now for some reason.
> 
> I have now created two new worksheets.
> MODEL TABLES & PART NUMBER TABLES.
> ...



error suggests code cannot find worksheet or table

If having difficulty setting this up place copy of your workbook with dummy data in a filesharing site like dropbox & provide a link to it & will have a look.

Dave


----------



## ipbr21054 (Sunday at 6:32 AM)

See attached thanks.

EXCEL FILE


----------



## dmt32 (Sunday at 6:56 AM)

Hi,
looks like you got yourself in to a bit of a pickle referencing the correct table worksheets

this works with sample workbook you posted


```
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 Tables")
 
     'set object variable to combobox model tables worksheet
    Set wsModelTables = ThisWorkbook.Worksheets("Model Tables")
 
    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
```

If you still get out of range errors check each the names of your tables to ensure they match naming convention

Dave


----------



## ipbr21054 (Sunday at 7:09 AM)

Ived used the code you have supplied above but i still get the same issue.

Ive suppl;ied image of my named worksheets

So were on the same page the worksheet names refer to this piece opf code here correct ?


```
'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")
```

If so i even renamed them to A & B but still the same


----------



## ipbr21054 (Saturday at 12:01 PM)

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 (Sunday at 7:15 AM)

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

Thanks for your time


----------



## dmt32 (Sunday at 7:18 AM)

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 (Sunday at 7:34 AM)

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 (Sunday at 7:49 AM)

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 (Sunday at 8:08 AM)

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

Dave


----------



## ipbr21054 (Sunday at 8:11 AM)

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 (Sunday at 8:18 AM)

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 (Sunday at 8:19 AM)

check each of the names in your tables

Dave


----------



## ipbr21054 (Sunday at 8:23 AM)

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


----------



## ipbr21054 (Sunday at 8:32 AM)

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

Please see if KaWASAKI & YAMAHA also fail for you


----------



## ipbr21054 (Saturday at 12:01 PM)

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 (Sunday at 8:58 AM)

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 (Sunday at 9:00 AM)

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 (Sunday at 9:14 AM)

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 (Sunday at 9:20 AM)

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 (Sunday at 11:32 AM)

Now that did the trick.

Many thanks for following this up


----------



## dmt32 (Sunday at 12:36 PM)

ipbr21054 said:


> Now that did the trick.
> 
> Many thanks for following this up


 welcome glad resolved

Dave


----------

