Dependent Active X Combo Boxes

[Alm5423]

Hey Everyone!

I'm trying to create a form with multiple Active X combo boxes where the selection in Combo Box 3 determines the list of drop down items in Combo Box 4.

Right now, Combo box 3 has two options: Type 1 or Type 2. If Type 1 is selected, I need Combo Box 4 (which currently contains a list of 8 items) to only show the first four that are related to Type 1. If Type 2 is selected, I need it to only show the second four options that are related to Type 2.

Does anyone know a VBA code that would allow me to do this? All of my Combo Box 4 drop down options are currently stored in a second tab on my worksheet.

 

[Fluff]

How about something like

Private Sub ComboBox3_Click()
   Select Case Me.ComboBox3.Value
      Case "Type 1": Me.ComboBox4.List = Sheets("Lists").Range("A2:A5").Value
      Case "Type 2": Me.ComboBox4.List = Sheets("Lists").Range("A6:A9").Value
   End Select
End Sub

 

[Alm5423]

Gave that a try. For some reason, I keep getting a "permission denied" error. The only thing I changed from your code above was swapping "Lists" for "Sheet 2" since that's the current name for my second tab.

 

[Alm5423]

Gave that a try. For some reason, I keep getting a "permission denied" error. The only thing I changed from your code above was swapping "Lists" for "Sheet 2" since that's the current name for my second tab.

Found the problem! I had previously populated the "List Fill Range" for Combo box 4 when I was planning to do this a different way. Once I removed that and used your formula above, it worked perfectly! Thank you so much!

 

[Alm5423]

Is there a way to add a third case for when Combo box 3 is blank? I essentially want to make it so that if Combo Box 3 is not filled out, there are no drop down options showing in Combo Box 4.

 

[Fluff]

Try using the change event instead like

Private Sub ComboBox3_Change()
   Select Case Me.ComboBox3.Value
      Case "Type 1": Me.ComboBox4.List = Sheets("Lists").Range("A2:A5").Value
      Case "Type 2": Me.ComboBox4.List = Sheets("Lists").Range("A6:A9").Value
      Case "": Me.ComboBox4.Clear
   End Select
End Sub

 

[Alm5423]

Try using the change event instead like

Private Sub ComboBox3_Change()
   Select Case Me.ComboBox3.Value
      Case "Type 1": Me.ComboBox4.List = Sheets("Lists").Range("A2:A5").Value
      Case "Type 2": Me.ComboBox4.List = Sheets("Lists").Range("A6:A9").Value
      Case "": Me.ComboBox4.Clear
   End Select
End Sub

Brilliant, that's much better. Last question. Let's say someone selects Type 1 in Combo Box 3 and chooses one of their drop menu options in Combo Box 4...but then decides they need to switch Combo Box 3 to Type 2 (or blank). Is there a way to clear Combo Box 4 anytime Combo Box 3 is changed after the initial selection? I hope that makes sense!

 

[Fluff]

Yup, you can use this

Private Sub ComboBox3_Change()
   Me.ComboBox4.Clear
   Select Case Me.ComboBox3.Value
      Case "Type 1": Me.ComboBox4.List = Sheets("Lists").Range("A2:A5").Value
      Case "Type 2": Me.ComboBox4.List = Sheets("Lists").Range("A6:A9").Value
   End Select
End Sub

 

[Alm5423]

Yup, you can use this

Private Sub ComboBox3_Change()
   Me.ComboBox4.Clear
   Select Case Me.ComboBox3.Value
      Case "Type 1": Me.ComboBox4.List = Sheets("Lists").Range("A2:A5").Value
      Case "Type 2": Me.ComboBox4.List = Sheets("Lists").Range("A6:A9").Value
   End Select
End Sub

That's the one I needed! Thank you!!

 

[Fluff]

Glad to help & thanks for the feedback.