Cascading Combo Box Indirect Population

Microsoft Excel 2007
November 10, 2009 at 05:27:13
Specs: Windows 7
Hi Guys - Is it possible to utilise the indirect function or a means using VBA to populate the listfill range of a combobox - this is also referred to as cascading combo boxes where the listfill range of a second combo box depends on what has been entered in the first combo box - Excel does not like the use of the indirect function in the listfillrange field in the combo box properties.

For example - say you select a 20 inch diameter pipe in the first combo box, the second combo box must display all the available wall thicknesses for the 20" pipe.

I don't want to use data validation because I want to display two columns in the second combo box which, for this example, would be wall thickness and its corresponding pipe schedule and combo boxes allow the display of two columns

See More: Cascading Combo Box Indirect Population

Report •

November 10, 2009 at 06:31:43
Look at this thread, especially the 4th post.

Report •

November 10, 2009 at 07:07:41
I don't do a lot with User Forms or Combo Boxes, but this was a chance to play. I modified the code found in the Mr. Excel thread I suggested in my previous post so that the values do not have to be hardcoded in VBA.

Using the data in the table below, I was able to use the following code to populate ComboBox1 with the values from A2:A4, and then populate ComboBox2 with the values from column B, C or D depending on the value in ComboBox1.

The code is highly dependent on the fact that the wall size Column matches the pipe size Row. I'm sure your data is not laid out like this, but the point is that there is probably a way to pull your data from cell ranges as opposed to hardcoding every value in the VBA code itself.

You could Select Case, or .Find(), or any of a number of different methods to accomplish your goal.

Let us know if we can be of further assitance.

     A          B          C         D    
1   Size    20" Walls  30" Walls  40" Walls
2    20"       1"         4"         7"
3    30"       2"         5"         8"
4    40"       3"         6"         9"

Private Sub UserForm_Activate()
    'AddItems for Combobox1 from A2:A4
      For ItemSize = 2 To 4
       ComboBox1.AddItem Cells(ItemSize, 1)
    'Set Visibilty for Combobox 2
     ComboBox2.Visible = False
End Sub

Private Sub ComboBox1_Change()
    'Combobox1 = "" Combobox2 = Hidden
    If ComboBox1 = "" Then
     ComboBox2.Visible = False     'Hidden
    End If
    'Populate Combobox2
    For ItemSize = 2 To 4
     If ComboBox1 = Cells(ItemSize, 1) Then
      ComboBox2.Visible = True      'Visible
      ComboBox2.Clear               'Clear Combobox
      'ComboBox AddItems based on ComboBox1
       For WallSize = 2 To 4
        ComboBox2.AddItem Cells(WallSize, ItemSize)
     End If
End Sub

Report •

November 10, 2009 at 07:28:05

The approach I used was a combination of a minimal amount of VBA, one formula and some named ranges.

Combobox 1 is linked to a list of pipe sizes, with its named ranges next to it (discussed later)

	A		B
6	Pipe size	Range name
7	10		SizeTen
8	12		SizeTwelve
9	14		SizeFourteen
10	16		SizeSixteen

The linked cell from Combobox 1 which contains the selected pipe size is cell A13 in this example.

On selecting a pipe size, A13 shows the pipe size and A15 uses a Vlookup to return the named range of cells containing the available pipe thicknesses (and associated pipe schedule info.) for that pipe size.

12	Size selection
13	12
14	Info. Range
15	SizeTwelve

Formula in A15:

The ranges of cells containing the pipe thickness/pipe schedule information for each pipe size are named.
For example in cells N5 to P9 is a table for pipe size 10, (with headings)
The range O6 to P9 contains the data to be displayed in Combobox 2 and is named SizeTen

	N		O			P
5	Pipe size	Wall thicknesses	Pipe schedule
6	10		0.2			AA
7	10		0.4			AB
8	10		0.6			AC
9	10		0.8			AD

The Combobox 1 change event has the following two lines of code:

Private Sub ComboBox1_Change()
ComboBox2.ListFillRange = Range(Worksheets("Sheet1").Range("A15")).Address
ComboBox2.ListRows = Range(Worksheets("Sheet1").Range("A15")).Rows.Count
End Sub

The first line of code populates Combobox 2 with the data for the selected pipe size/pipe schedule, and the second line calculates how many rows of data there are in that pipe size's table, and uses that to set the size of the drop down.

Hope this gives you an outline to work from.


Report •

Related Solutions

November 10, 2009 at 09:01:17
Thanks Humar
it was the line
ComboBox2.ListFillRange = Range(Worksheets("Sheet1").Range("A15")).Address

which suplied the key to open the lock.
Thanks to DerbyDad as well

Much Appreciated - my problem is solved

Report •

November 10, 2009 at 09:07:20
You're Welcome

Report •

Ask Question