Populating combobox from table based on the header selected in another combobox (some data on different sheets)

[BWL388]

Hello,

I am trying to set populate a combobox with a column whose header matches the selected value of another combobox. Also due to using more than one table, on more than one sheet, I am having problems retrieving the correct data on the dependent comboboxes due to the active sheet. I have tried about 30 different ways adapted from resources found online. However, none have worked across 2+ sheets without heavily relying on "Select." Below is an example of the table setup.

In Combobox1: A, B, or C would be selected (These are used as the headers for Table1). On that change, Combobox2 would be populated from the options below the previously selected value. Due to the change in Combobox2, Combobox3 will be populated by a range of values based on Combobox2. However, due to the limitations of the tables, the third box must be populated from a separate table(2) that is on a separate sheet(2).

Table 1 on Sheet 1​

Group A	Group B	Group C
A1	B1	C1
A2	B2	C2
A3	B3	C3

Table 2 on Sheet 2​

Detail 1	Detail 2	Detail 3
1A	2A	3A
1B	2B	3B
1C	2C	3C

Thanks in advance for any assistance that can be provided.

 

[DanteAmor]

Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

Continuing with your example, I assume that combobox1 has the data:
Group A, Group B and Group C.
If you select Group A, then combobox2 is filled with the data: A1, A2 and A3.
But if from combobox2 you select data A1, then what data do you want to fill in combobox3?

If you select Group B, then combobox2 is filled with the data: B1, B2 and B3.
But if from combobox2 you select data B2, then what data do you want to fill in combobox3?

If you select Group B, then combobox2 is filled with the data: B1, B2 and B3.
But if from combobox2 you select data B3, then what data do you want to fill in combobox3?

If you select Group C, then combobox2 is filled with the data: C1, C2 and C3.
But if from combobox2 you select data C1, then what data do you want to fill in combobox3?

Could you complete the examples to understand what the pattern is to fill out the combobox3?

I hope to hear from you soon.
Respectfully
Dante Amor

 

[BWL388]

Thank you for your response. I apologize for the incomplete entry. I was in a bit of a hurry.

You have the basis for what I'm trying to do correct. I obviously forgot to include the separate sheet issue. Combobox3 would be populated from Table2 on Sheet2, but based on Combobox2, just as Combobox2 is based on Combobox1. The problem I'm having is that none of the options I've found online, or tried by "hybridizing" some of those online options, have both populated the associated comboboxes and avoided overuse of the Activate or Select functions. Of course, I am very limited in my experience with VBA, so no surprise there.

Combobox1 = Sheet1; Table1; Group A
Leading to A1-A3 for Combobox2
Combobox2 = Sheet1; Table1; Group A; A3
Limiting Combobox3 to a range on Sheet2; Table2
Combobox3 = Sheet2; Table2; Detail3
Limiting other components in following areas to options in Sheet2; Table2; Detail3; 3A-3C

I'm not sure if this clears anything up, but this is essentially the plan. That is, if I can get it done.

 

[DanteAmor]

I don't understand your example. With a single example it is difficult to find a pattern.
If you are not in a hurry and really want help, you could give more examples.

For example, for the following cases, what data do you want to fill in combobox3:

If you select Group A, then combobox2 is filled with the data: A1, A2 and A3.
But if from combobox2 you select data A1, then what data do you want to fill in combobox3?

If you select Group B, then combobox2 is filled with the data: B1, B2 and B3.
But if from combobox2 you select data B1, then what data do you want to fill in combobox3?

If you select Group C, then combobox2 is filled with the data: C1, C2 and C3.
But if from combobox2 you select data C1, then what data do you want to fill in combobox3?

 

[BWL388]

Everything will populate from left to right to ensure uniformity in operation and description. This is so the log of entries is clean, manageable, and readable. Any details that do not pertain to a certain tool's description will be skipped. However, those conditions have not been a problem. I have only had issues with pulling data to populate the form from 2+ sheets without heavy use of "Select."

Tools​

Wrenches	Screwdrivers	Bits
Combination	Phillips	Drill Bit
Box Ratchet	Flathead	End Mill Bit
Crescent	Torx	Step Bit

Dimensions​

Size	Head	Length	Material / Finish
1/8in	#3	2in	Black Oxide
1/4in	#2	3in	Titanium Nitride
3/8in	#1	4in	Solid Carbide
1/2in	#0	5in	High Speed Steel
9/16in	#00	6in	Stainless Steel
3/4in	#000	8in	Chrome Vanadium

Scenario 1
Combobox1: User picks "Screwdrivers"
____Combobox2 is populated by the column below "Screwdrivers"
Combobox2: User picks "Phillips"
____Combobox3 is populated by the column below "Head" (from Sheet2; Dimensions Table, and based on conditions that dictate the options available based on the specific tool that's picked)
Combobox3: User picks "#2"
____Combobox4 is populated by the column below "Length"
Combobox4: User picks "4in"
ect...
Since Screwdrivers are not available to this user in any special finish, "Material/Finish" does not populate a box.

Scenario 2

Combobox1: User picks "Bits"
____Combobox2 is populated by the column below "Bits"
Combobox2: User picks "End Mill"
____Combobox3 is populated by the column below "Size"
Combobox3: User picks "9/16in"
____Combobox4 is populated by the column under "Length"
etc...

 

[DanteAmor]

Combobox3 is populated by the column below "Head" (from Sheet2; Dimensions Table, and based on conditions that dictate the options available based on the specific tool that's picked)

And where are those bases to know if the combobox3 will be filled with the data that is under "Head" or "Size" or another?

Your specification is incomplete.

You must specify somewhere on your sheet something like the following:

Libro1
	A	B
1	Combobox1	Combobox3
2	Screwdrivers	Head
3	Bits	Size
4	etc	etc
Bases

I have only had issues with pulling data to populate the form from 2+ sheets without heavy use of "Select."

I show you a way to fill the combos, without having to select the sheet, or select the range of cells.
The following works if you actually have table1 and table2, as you mentioned in your posts:

Table1 on sheet1:

varios 05mar2024.xlsm
	A	B	C
1	Wrenches	Screwdrivers	Bits
2	Combination	Phillips	Drill Bit
3	Box Ratchet	Flathead	End Mill Bit
4	Crescent	Torx	Step Bit
Tools

Table2 on sheet2:

varios 05mar2024.xlsm
	A	B	C	D
1	Size	Head	Length	Material / Finish
2	1/8in	#3	2in	Black Oxide
3	1/4in	#2	3in	Titanium Nitride
4	3/8in	#1	4in	Solid Carbide
5	1/2in	#0	5in	High Speed Steel
6	9/16in	#00	6in	Stainless Steel
7	3/4in	#000	8in	Chrome Vanadium
Dimensions

Replace all your code with the following and try:

Option Explicit

Private Sub ComboBox1_Change()
  Dim tbl As ListObject
  
  ComboBox2.Clear
  ComboBox2.Value = ""
  ComboBox3.Clear
  ComboBox3.Value = ""
  
  Set tbl = Range("Table1").ListObject  'Fit to your table 1 name
  With ComboBox1
    If .Value = "" Or .ListIndex = -1 Then Exit Sub
    
    'fill combobox2 without selecting the sheet
    ComboBox2.List = tbl.ListColumns(.Value).DataBodyRange.Value
 End With
End Sub

Private Sub ComboBox2_Change()
  Dim tbl As ListObject
  
  ComboBox3.Clear
  ComboBox3.Value = ""
  
  Set tbl = Range("Table2").ListObject  'Fit to your table 2 name
  With ComboBox2
    If .Value = "" Or .ListIndex = -1 Then Exit Sub
    
    'fill combobox3 without selecting the sheet
    Select Case ComboBox1.Value
      Case "Screwdrivers"
        ComboBox3.List = tbl.ListColumns("Head").DataBodyRange.Value
      Case "Bits"
        ComboBox3.List = tbl.ListColumns("Size").DataBodyRange.Value
      Case "Other"
        'Here is the list you need
    End Select
 End With
End Sub

Private Sub UserForm_Activate()
  ComboBox1.List = Application.Transpose(Range("Table1[#Headers]").Value)
End Sub

I hope the above helps you with what you need.


I would like to continue helping, but on two occasions my examples have not been answered, the information is not complete. If someone else already understood what the OP needs, I hope they can help.

 

[BWL388]

OK, so I think you may be overthinking my issue. Not the solution, but what I need to fix it. The selection process for which column to use isn't the issue, just accessing the column that has been chosen. On that note, You have given me, not the exact solution, but the necessary components. I still have to play with the details a bit, but you have me on the right track and the boxes are acting the way I need them to.

Thank you very much for your help.

 

[BWL388]

OK, so I think you may be overthinking my issue. Not the solution, but what I need to fix it. The selection process for which column to use isn't the issue, just accessing the column that has been chosen. On that note, You have given me, not the exact solution, but the necessary components. I still have to play with the details a bit, but you have me on the right track and the boxes are acting the way I need them to.

Thank you very much for your help.

Sorry, wrong way to say that: Not the solution, but what part of the build is the problem.