Help with Cascading ComboBox's

[Gregm66]

Hi everyone,
i am sure that this question has been asked before and i will appologise now if that is so.
i have searched for the answer and had no luck with achieving my objective.
i will attempt to explain this and make it easy to understand, any help with this would be appreciated.
Thanks in Advance

I have a UserForm that holds 3 ComboBox's and several textbox's. My problem is that i am trying to get my third combobox to populate based on the second combobox selection. ComboBox1 ("cboSelect") has a list of 2 items Movies and Music, If Movies is selected then ComboBox2 ("cboCategory") is populated with a list of movie categorys IE: Action, History, Horror etc, or if Music is selected then it displays Rock, Hip Hop, Classical etc these 2 combobox's are functioning perfectly fine. ComboBox3 ("cboName") howerver needs to then populate with only the matching values. So if i select 1.Movies - 2.Action then ComboBox3 ("cboName") would only show all of the movies that are in the action category.

Sheet name is MovieList&Details this has 17 Columns. Column A is the Movie Name, Column B shows if it is Action or Horror etc. This sheet is for Movies Only.
Sheet Named MusicList again has 17 Columns. Column A is the Song Name and Column B is the Genre, Rock, Hip Hop etc.

So cboName has to look at 2 different sheets depending on the selection made in cboCategory and filter the results so that only items matching cboCategory selection are shown.

Hope that is easy to understand.

Thanks Again

 

[My Aswer Is This]

So is RowSource Music a named range named Music

When I tried that I received the same error you received.

 

[My Aswer Is This]

I have not used RowSource in so long I forgot what it was.

See that's why my script had this line of code:

Private Sub UserForm_Initialize()
cboSelect.AddItem "Movies"
cboSelect.AddItem "Music"
End Sub

So with you setting Rowsource and me using a script to add values to the Combobox this may have caused the error.

So use one or the other but not both.

 

[Gregm66]

thanks for your help My Answer Is,

i got it sorted out now cheers.
i have attached the working for you.

Option Explicit
Dim Dic As Object

Private Sub cboSelect_Change()
Select Case Me.cboSelect
    Case "Movies"
        Me.cboCategory.RowSource = "Movies"
    Case "Music"
        Me.cboCategory.RowSource = "Music"
    Case Else
End Select
End Sub

Private Sub UserForm_Initialize()
Dim Cl As Range
   Set Dic = CreateObject("scripting.dictionary")
   With Sheets("MovieList&Details")
      Dic.Add "Movies", CreateObject("scripting.dictionary")
      For Each Cl In .Range("C2", .Range("C" & Rows.Count).End(xlUp))
         If Not Dic("Movies").exists(Cl.Value) Then
            Dic("Movies").Add Cl.Value, CreateObject("scripting.dictionary")
            Dic("Movies")(Cl.Value).Add Cl.Offset(, -1).Value, Nothing
         ElseIf Not Dic("Movies")(Cl.Value).exists(Cl.Offset(, -1).Value) Then
            Dic("Movies")(Cl.Value).Add Cl.Offset(, -1).Value, Nothing
         End If
      Next Cl
   End With
   With Sheets("MusicList")
      Dic.Add "Music", CreateObject("scripting.dictionary")
      For Each Cl In .Range("B2", .Range("B" & Rows.Count).End(xlUp))
         If Not Dic("Music").exists(Cl.Value) Then
            Dic("Music").Add Cl.Value, CreateObject("scripting.dictionary")
            Dic("Music")(Cl.Value).Add Cl.Offset(, -1).Value, Nothing
         ElseIf Not Dic("Music")(Cl.Value).exists(Cl.Offset(, -1).Value) Then
            Dic("Music")(Cl.Value).Add Cl.Offset(, -1).Value, Nothing
         End If
      Next Cl
   End With
   Me.cboSelect.List = Dic.keys
    With Application
  .WindowState = xlMaximized
  Zoom = Int(.Width / Me.Width * 100)
  Width = .Width
  Height = .Height
  End With
End Sub
Private Sub cboCategory_Click()
   Me.cboName.Clear
   Me.cboName.List = Dic(Me.cboSelect.Value)(Me.cboCategory.Value).keys
End Sub

Again thanks for all your help.

 

[Gregm66]

thanks for all your help guys below is the code that is now working.

Option Explicit
Dim Dic As Object

Private Sub cboSelect_Change()
Select Case Me.cboSelect
    Case "Movies"
        Me.cboCategory.RowSource = "Movies"
    Case "Music"
        Me.cboCategory.RowSource = "Music"
    Case Else
End Select
End Sub

Private Sub UserForm_Initialize()
Dim Cl As Range
   Set Dic = CreateObject("scripting.dictionary")
   With Sheets("MovieList&Details")
      Dic.Add "Movies", CreateObject("scripting.dictionary")
      For Each Cl In .Range("C2", .Range("C" & Rows.Count).End(xlUp))
         If Not Dic("Movies").exists(Cl.Value) Then
            Dic("Movies").Add Cl.Value, CreateObject("scripting.dictionary")
            Dic("Movies")(Cl.Value).Add Cl.Offset(, -1).Value, Nothing
         ElseIf Not Dic("Movies")(Cl.Value).exists(Cl.Offset(, -1).Value) Then
            Dic("Movies")(Cl.Value).Add Cl.Offset(, -1).Value, Nothing
         End If
      Next Cl
   End With
   With Sheets("MusicList")
      Dic.Add "Music", CreateObject("scripting.dictionary")
      For Each Cl In .Range("B2", .Range("B" & Rows.Count).End(xlUp))
         If Not Dic("Music").exists(Cl.Value) Then
            Dic("Music").Add Cl.Value, CreateObject("scripting.dictionary")
            Dic("Music")(Cl.Value).Add Cl.Offset(, -1).Value, Nothing
         ElseIf Not Dic("Music")(Cl.Value).exists(Cl.Offset(, -1).Value) Then
            Dic("Music")(Cl.Value).Add Cl.Offset(, -1).Value, Nothing
         End If
      Next Cl
   End With
   Me.cboSelect.List = Dic.keys
    With Application
  .WindowState = xlMaximized
  Zoom = Int(.Width / Me.Width * 100)
  Width = .Width
  Height = .Height
  End With
End Sub
Private Sub cboCategory_Click()
   Me.cboName.Clear
   Me.cboName.List = Dic(Me.cboSelect.Value)(Me.cboCategory.Value).keys
End Sub

Thanks again for all your help

Regards
Gregm66

 

[My Aswer Is This]

Looks like you decided to use script provided by Fluff. Take care

 

[Fluff]

Glad you got it sorted & thanks for the feedback