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

 

[Micron]

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]

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]

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

Thanks

 

[dmt32]

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.xls
	A	B	C	D	E
1	Honda		KAWASAKI		YAMAHA
2	HON Part1		KW Part1		YAM Part1
3	HON Part2		KW Part2		YAM Part2
4	HON Part3		KW Part3		YAM Part3
5	HON Part4		KW Part4		YAM Part4
6	HON Part6		KW Part6		YAM Part6
7	HON Part5		KW Part5		YAM Part5
8	HON Part7		KW Part7		YAM Part7
Parts Tables

 

[ipbr21054]

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]

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

Thanks

 

[Micron]

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]

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.xls
	A	B	C	D	E
1	Honda		KAWASAKI		YAMAHA
2	HON Part1		KW Part1		YAM Part1
3	HON Part2		KW Part2		YAM Part2
4	HON Part3		KW Part3		YAM Part3
5	HON Part4		KW Part4		YAM Part4
6	HON Part6		KW Part6		YAM Part6
7	HON Part5		KW Part5		YAM Part5
8	HON Part7		KW Part7		YAM Part7
Parts Tables

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]

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