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]

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

Tables will be names like so,
HondaModels
KawasakiModels
YamahaModels

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]

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]

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

Dave

 

[ipbr21054]

See here

TEST.xlsm
	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	HONDA PN	KAWASAKI PN	SUZUKI TABLE	YAMAHA PN	PIAGGIO PN	APRILIA PN					HONDA M	KAWASAKI M	SUZUKI M	YAMAHA M	PIAGGIO M	APRILLIA M
2	35121-MAT-E00	270080615	37146-26D10-000	5SL-82511-08-00							CB 1000	ZZR 1400	GSXR 1000 RR	MT09	E	F
3	35121-MBW-601		37146-33E00-000								CB 1300		GSXR 600	R1
4	35121-MCA-811										CB 500 F		ST 1300	XJR 1300
5	35121-MCJ-D00										CB 500 X		RF 900	XVS 1300
6	35121-MEH-631										CB 600 F
7	35121-MFJ-D00										CB 600 N
8	35121-MFL-641										CB 650 F
9	35121-MFN-D01										CB 900 F
10	35121-MGH-641										CBF 1000
11	35121-MGP-D01										CBF 600
12	35121-MGP-D63										CBR 1000
13	35121-MGS-A31										CBR 1000 RR
14	35121-MJE-D02										CBR 1100 XX
15	35121-MJG-641										CBR 600 F
16	72147-MCA-305										CBR 600 R
17	72147-MCA-315										CBR 600 RR
18	72147-MCA-671										CBR 650 F
19	72147-MJK-305										CBR 650 R
20	72147-MKJ-326										CBR 900 RR
21											CBR 954
22											GOLD WING
23											NC 700 S
24											NC 700 X
25											NC 750
26											NC 750 X
27											NT 650
28											NT 700
29											SILVER WING
30											SH 300 A
31											ST 1300
32											VALKYRIE
33											VFR 1200
34											VFR 800
35											VTR 1000
36											X11
37											XL 650
38											XL 700
39											AFRICA TWIN
DATABASE

 

[dmt32]

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.xls
	A	B	C	D	E
1	Honda		KAWASAKI		YAMAHA
2	HON Model		KW Model		YAM Model
3	HON Model		KW Model		YAM Model
4	HON Model		KW Model		YAM Model
5	HON Model		KW Model		YAM Model
6	HON Model		KW Model		YAM Model
7	HON Model		KW Model		YAM Model
8	HON Model		KW Model		YAM Model
Model Tables

Tables should be named as per you naming suggestion

HondaModels
KawasakiModels
YamahaModels

Dave

 

[ipbr21054]

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]

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

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]

See attached thanks.

EXCEL FILE

 

[dmt32]

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]

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