Multiple Dependent ComboBoxes in UserForm

D

ddhsock

New Member
Joined
Jan 13, 2020
Messages
27
Office Version
  1. 365
Platform
  1. Windows
I have been trying to build a user form with dependent ComboBoxes. I have successfully created two but I cannot get the third to work. Here is the code for the first two that works.

VBA Code: 
Private Sub ComboBox1_Change()

    Application.EnableEvents = False

    ComboBox2.Clear

    Application.EnableEvents = True

  

    Select Case ComboBox1.Value

        Case "Blocking"

            ComboBox2.AddItem "Roof Edge"

            ComboBox2.AddItem "Toilet Acc/Partition"

            ComboBox2.AddItem "Millwork"

            ComboBox2.AddItem "Door Frames"

            ComboBox2.AddItem "Chalkboards"

            ComboBox2.AddItem "Misc."

        Case "Framing"

            ComboBox2.AddItem "Walls"

            ComboBox2.AddItem "Joist"

            ComboBox2.AddItem "Roofs"

            ComboBox2.AddItem "Ceilings"

            ComboBox2.AddItem "Platforms"

            ComboBox2.AddItem "Accessories"

            ComboBox2.AddItem "Stairs"

        Case "Bolts"

            ComboBox2.AddItem "Wedge/Expansion"

            ComboBox2.AddItem "Machine"

        Case "Furring"

            ComboBox2.AddItem "Floor Furring"

            ComboBox2.AddItem "Wall Furring"

      

        End Select

   

 

          

   

          

  

End Sub

Here is code for the third ComboBox that I cannot get to run. I will also be needing to add additional ComboBoxes:
  Private Sub ComboBox2_Change()

    Application.EnableEvents = False

    ComboBox3.Clear

    Application.EnableEvents = True

        Case "Roof Edge"

            ComboBox3.AddItem "Bolted"

            ComboBox3.AddItem "Nailed"

            ComboBox3.AddItem "Shaped/Ripped"

        Case "Toilet Acc/Partition"

            ComboBox3.AddItem "Kiln Dried"

            ComboBox3.AddItem "Fire Treated"

        Case "Millwork"

             ComboBox3.AddItem "Kiln Dried"

            ComboBox3.AddItem "Fire Treated "

        Case "Door Frames"

ComboBox3.AddItem "Kiln Dried"

            ComboBox3.AddItem "Fire Treated

        Case "Chalkboards"

            ComboBox3.AddItem "Kiln Dried"

            ComboBox3.AddItem "Fire Treated

         Case "Misc."

ComboBox3.AddItem "Kiln Dried"

            ComboBox3.AddItem "Fire Treated          

        End Select

Any help would be appreciated,
 
Last edited by a moderator:

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
I recommend you to create a database with all possible combinations. It will be more practical to make revisions and corrections on an excel sheet than in the code.
For example:

Book1
ABC
1COMBO1COMBO2COMBO3
2BlockingRoof EdgeBolted
3BlockingRoof EdgeNailed
4BlockingRoof EdgeShaped/Ripped
5BlockingToilet Acc/PartitionKiln Dried
6BlockingToilet Acc/PartitionFire Treated
7BlockingMillworkKiln Dried
8BlockingMillworkFire Treated
9FramingWallssome
10FramingJoistother
Combos


____________________________________________________________
The code to load the combos would be:

VBA Code: 
Option Explicit
Dim sh As Worksheet

Private Sub ComboBox1_Change()
  Dim c As Range, dic As Object
  Set dic = CreateObject("Scripting.Dictionary")
  
  ComboBox2.Clear
  ComboBox3.Clear
  
  For Each c In sh.Range("A2", sh.Range("A" & Rows.Count).End(xlUp))
    If c.Value = ComboBox1.Value Then
      dic(c.Offset(0, 1).Value) = Empty
    End If
  Next
  ComboBox2.List = dic.keys
End Sub

Private Sub ComboBox2_Change()
  Dim c As Range, dic As Object
  ComboBox3.Clear
  
  For Each c In sh.Range("A2", sh.Range("A" & Rows.Count).End(xlUp))
    If c.Value = ComboBox1.Value And c.Offset(0, 1).Value = ComboBox2.Value Then
      ComboBox3.AddItem c.Offset(, 2).Value
    End If
  Next
End Sub

Private Sub UserForm_Activate()
  Dim c As Range, dic As Object
  Set sh = Sheets("Combos")
  Set dic = CreateObject("Scripting.Dictionary")
  For Each c In sh.Range("A2", sh.Range("A" & Rows.Count).End(xlUp))
    dic(c.Value) = Empty
  Next
  ComboBox1.List = dic.keys
End Sub

The result would be something like this:

1579651126841.png
 
Upvote 0
DanteAmor said:
I recommend you to create a database with all possible combinations. It will be more practical to make revisions and corrections on an excel sheet than in the code.
For example:

Book1
ABC
1COMBO1COMBO2COMBO3
2BlockingRoof EdgeBolted
3BlockingRoof EdgeNailed
4BlockingRoof EdgeShaped/Ripped
5BlockingToilet Acc/PartitionKiln Dried
6BlockingToilet Acc/PartitionFire Treated
7BlockingMillworkKiln Dried
8BlockingMillworkFire Treated
9FramingWallssome
10FramingJoistother
Combos


____________________________________________________________
The code to load the combos would be:

VBA Code: 
Option Explicit
Dim sh As Worksheet

Private Sub ComboBox1_Change()
  Dim c As Range, dic As Object
  Set dic = CreateObject("Scripting.Dictionary")
 
  ComboBox2.Clear
  ComboBox3.Clear
 
  For Each c In sh.Range("A2", sh.Range("A" & Rows.Count).End(xlUp))
    If c.Value = ComboBox1.Value Then
      dic(c.Offset(0, 1).Value) = Empty
    End If
  Next
  ComboBox2.List = dic.keys
End Sub

Private Sub ComboBox2_Change()
  Dim c As Range, dic As Object
  ComboBox3.Clear
 
  For Each c In sh.Range("A2", sh.Range("A" & Rows.Count).End(xlUp))
    If c.Value = ComboBox1.Value And c.Offset(0, 1).Value = ComboBox2.Value Then
      ComboBox3.AddItem c.Offset(, 2).Value
    End If
  Next
End Sub

Private Sub UserForm_Activate()
  Dim c As Range, dic As Object
  Set sh = Sheets("Combos")
  Set dic = CreateObject("Scripting.Dictionary")
  For Each c In sh.Range("A2", sh.Range("A" & Rows.Count).End(xlUp))
    dic(c.Value) = Empty
  Next
  ComboBox1.List = dic.keys
End Sub

The result would be something like this:

View attachment 4581
Click to expand...
I have seen some similar examples. I have a lot of code to add. I am definitely going to look at your suggestion. Thank you very much.
 
Upvote 0
ddhsock said:
I have seen some similar examples. I have a lot of code to add. I am definitely going to look at your suggestion. Thank you very much.
Click to expand...

Of course, let me know if you have an doubt.
 
Upvote 0
I followed your recommendation and created spreadsheet with all possible combinations (nearly 500 rows). As you can see I have added more Comboboxes and they are working, however in ComboBoxes 3 ,4 & 5 I am getting many duplicate entries. This does not happen in ComboBox 1 or 2. I can not see anything in the code that prevents duplicates in ComboBox 1 & 2 that is missing for the code for ComboBoxes 3,4,& 5. Your guidance would be greatly appreciated. Thank You.

Below is the code I have now.


VBA Code: 
Option Explicit
Dim sh As Worksheet
Private Sub ComboBox1_Change()
Dim c As Range, dic As Object
Set dic = CreateObject("Scripting.Dictionary")

ComboBox2.Clear
ComboBox3.Clear
ComboBox4.Clear
ComboBox5.Clear

For Each c In sh.Range("A2", sh.Range("A" & Rows.Count).End(xlUp))
If c.Value = ComboBox1.Value Then
dic(c.Offset(0, 1).Value) = Empty
End If
Next
ComboBox2.List = dic.keys
End Sub
Private Sub ComboBox2_Change()
Dim c As Range, dic As Object
ComboBox3.Clear

For Each c In sh.Range("A2", sh.Range("A" & Rows.Count).End(xlUp))
If c.Value = ComboBox1.Value And c.Offset(0, 1).Value = ComboBox2.Value Then
ComboBox3.AddItem c.Offset(, 2).Value
End If
Next
End Sub
Private Sub ComboBox3_Change()
Dim c As Range, dic As Object
ComboBox4.Clear

For Each c In sh.Range("A2", sh.Range("A" & Rows.Count).End(xlUp))
If c.Value = ComboBox1.Value And c.Offset(0, 1).Value = ComboBox2.Value And c.Offset(0, 2).Value = ComboBox3.Value Then
ComboBox4.AddItem c.Offset(, 3).Value


End If
Next
End Sub
Private Sub ComboBox4_Change()
Dim c As Range, dic As Object
ComboBox5.Clear

For Each c In sh.Range("A2", sh.Range("A" & Rows.Count).End(xlUp))
If c.Value = ComboBox1.Value And c.Offset(0, 1).Value = ComboBox2.Value And c.Offset(0, 2).Value = ComboBox3.Value And c.Offset(0, 3).Value = ComboBox4.Value Then
ComboBox5.AddItem c.Offset(, 4).Value


End If
Next
End Sub
Private Sub UserForm_Activate()
Dim c As Range, dic As Object
Set sh = Sheets("Task Database")
Set dic = CreateObject("Scripting.Dictionary")
For Each c In sh.Range("A2", sh.Range("A" & Rows.Count).End(xlUp))
dic(c.Value) = Empty
Next
ComboBox1.List = dic.keys
End Sub
 
Last edited by a moderator:
Upvote 0
ddhsock said:
nearly 500 rows
Click to expand...

500 lines in the macro would have been crazy ? !

Try this

VBA Code: 
Option Explicit
Dim sh As Worksheet

Private Sub ComboBox1_Change()
  Dim c As Range, dic As Object
  Set dic = CreateObject("Scripting.Dictionary")
 
  ComboBox2.Clear
  ComboBox3.Clear
  ComboBox4.Clear
  ComboBox5.Clear
 
  For Each c In sh.Range("A2", sh.Range("A" & Rows.Count).End(xlUp))
    If c.Value = ComboBox1.Value Then
      dic(c.Offset(0, 1).Value) = Empty
    End If
  Next
  ComboBox2.List = dic.keys
End Sub

Private Sub ComboBox2_Change()
  Dim c As Range, dic As Object
  Set dic = CreateObject("Scripting.Dictionary")
 
  ComboBox3.Clear
  ComboBox4.Clear
  ComboBox5.Clear
 
  For Each c In sh.Range("A2", sh.Range("A" & Rows.Count).End(xlUp))
    If c.Value = ComboBox1.Value And c.Offset(0, 1).Value = ComboBox2.Value Then
      dic(c.Offset(0, 2).Value) = Empty
    End If
  Next
  ComboBox3.List = dic.keys
End Sub

Private Sub ComboBox3_Change()
  Dim c As Range, dic As Object
  Set dic = CreateObject("Scripting.Dictionary")
 
  ComboBox4.Clear
  ComboBox5.Clear
 
  For Each c In sh.Range("A2", sh.Range("A" & Rows.Count).End(xlUp))
    If c.Value = ComboBox1.Value And c.Offset(0, 1).Value = ComboBox2.Value And _
       c.Offset(0, 2).Value = ComboBox3.Value Then
      dic(c.Offset(0, 3).Value) = Empty
    End If
  Next
  ComboBox4.List = dic.keys
End Sub

Private Sub ComboBox4_Change()
  Dim c As Range, dic As Object
  Set dic = CreateObject("Scripting.Dictionary")
 
  ComboBox5.Clear
 
  For Each c In sh.Range("A2", sh.Range("A" & Rows.Count).End(xlUp))
    If c.Value = ComboBox1.Value And c.Offset(0, 1).Value = ComboBox2.Value And _
       c.Offset(0, 2).Value = ComboBox3.Value And c.Offset(0, 3).Value = ComboBox4.Value Then
      dic(c.Offset(0, 4).Value) = Empty
    End If
  Next
  ComboBox5.List = dic.keys
End Sub

Private Sub UserForm_Activate()
  Dim c As Range, dic As Object
  Set sh = Sheets("Task Database")
  Set dic = CreateObject("Scripting.Dictionary")
  For Each c In sh.Range("A2", sh.Range("A" & Rows.Count).End(xlUp))
    dic(c.Value) = Empty
  Next
  ComboBox1.List = dic.keys
End Sub
 
Upvote 0
DanteAmor said:
500 lines in the macro would have been crazy ? !

Try this

VBA Code: 
Option Explicit
Dim sh As Worksheet

Private Sub ComboBox1_Change()
  Dim c As Range, dic As Object
  Set dic = CreateObject("Scripting.Dictionary")

  ComboBox2.Clear
  ComboBox3.Clear
  ComboBox4.Clear
  ComboBox5.Clear

  For Each c In sh.Range("A2", sh.Range("A" & Rows.Count).End(xlUp))
    If c.Value = ComboBox1.Value Then
      dic(c.Offset(0, 1).Value) = Empty
    End If
  Next
  ComboBox2.List = dic.keys
End Sub

Private Sub ComboBox2_Change()
  Dim c As Range, dic As Object
  Set dic = CreateObject("Scripting.Dictionary")

  ComboBox3.Clear
  ComboBox4.Clear
  ComboBox5.Clear

  For Each c In sh.Range("A2", sh.Range("A" & Rows.Count).End(xlUp))
    If c.Value = ComboBox1.Value And c.Offset(0, 1).Value = ComboBox2.Value Then
      dic(c.Offset(0, 2).Value) = Empty
    End If
  Next
  ComboBox3.List = dic.keys
End Sub

Private Sub ComboBox3_Change()
  Dim c As Range, dic As Object
  Set dic = CreateObject("Scripting.Dictionary")

  ComboBox4.Clear
  ComboBox5.Clear

  For Each c In sh.Range("A2", sh.Range("A" & Rows.Count).End(xlUp))
    If c.Value = ComboBox1.Value And c.Offset(0, 1).Value = ComboBox2.Value And _
       c.Offset(0, 2).Value = ComboBox3.Value Then
      dic(c.Offset(0, 3).Value) = Empty
    End If
  Next
  ComboBox4.List = dic.keys
End Sub

Private Sub ComboBox4_Change()
  Dim c As Range, dic As Object
  Set dic = CreateObject("Scripting.Dictionary")

  ComboBox5.Clear

  For Each c In sh.Range("A2", sh.Range("A" & Rows.Count).End(xlUp))
    If c.Value = ComboBox1.Value And c.Offset(0, 1).Value = ComboBox2.Value And _
       c.Offset(0, 2).Value = ComboBox3.Value And c.Offset(0, 3).Value = ComboBox4.Value Then
      dic(c.Offset(0, 4).Value) = Empty
    End If
  Next
  ComboBox5.List = dic.keys
End Sub

Private Sub UserForm_Activate()
  Dim c As Range, dic As Object
  Set sh = Sheets("Task Database")
  Set dic = CreateObject("Scripting.Dictionary")
  For Each c In sh.Range("A2", sh.Range("A" & Rows.Count).End(xlUp))
    dic(c.Value) = Empty
  Next
  ComboBox1.List = dic.keys
End Sub
Click to expand...

Thank you so much, that seems to work. I will have to study this to try to understand what made the difference.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Code to use multiple trigger cells to hide / unhide rows of corresponding question sets
Replies
1
Views
802
dmt32
D
M
  • Question
Combobox values based on combo values..... Based on a third combo box value?
Replies
2
Views
426
Mr Parker
M
D
  • Question
Transfering data over multiple Workbooks; Excel 2010
Replies
0
Views
831
Da3d4lu5
D

Forum statistics

Threads
1,216,085
Messages
6,128,732
Members
449,465
Latest member
TAKLAM

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top