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]

Please show me the scripts you already have that loads the values into Combobox

cboCategory​

This will help me setup a similar Userform to work with.

And define for me what you mean by Cascading

 

[Gregm66]

Thanks for replying My Aswer Is This.
Code Follows Bellow.

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

Private Sub UserForm_Initialize()
    'Declare Variables
    Dim LRow As Long
    
    'Define Last Row
    LRow = Sheets("List1").Cells(Sheets("List1").Rows.Count, "A").End(xlUp).Row
    'Load Category Values from MyLists
    With Me
        .cboSelect.RowSource = "List1!" & Sheets("List1").Range("A1:A" & LRow).Address
        .cboSelect.ListIndex = 0 'Set cboCategory Value to first in list

 

[Gregm66]

My appologise

My Aswer Is This,

Cascading i proberbly should have said that cboName needs to filter its results, and only return values that match cboCatergory
​

 

[My Aswer Is This]

Why are you using this:

LRow = Sheets("List1")​

In your original post you said your sheet names were:

Sheet name is MovieList&Details

Sheet Named MusicList​

_{^{<strike>
</strike>}}​

 

[Gregm66]

i will double check that code sorry. i have been at this all day and have code everywhere at the moment.
i know that cmbSelect uses RowSource in the propertys value. and that is RowSource = Category

the first lines of code that i posted are populating cboCategory based on cboSelect.
Please ignore the second lines of code posted.

 

[My Aswer Is This]

That's OK if you do not know the sheet names.
But then I cannot setup a sample Userform for me to work with.
Maybe someone else here at Mr. Excel will be able to help you.

 

[Gregm66]

the sheet names in my first and original post are correct.
the sheet that you mention ("List1") does exsist but is not used, that line of code was an original attempt populating cmbCategory that failed.
forget that sheet. i deleted those lines of code as they were not needed.

MovieList&Details and MusicList are the 2 sheets that i am using.

Regards
GregM

 

[Fluff]

How about

[COLOR=#ff0000]Option Explicit
Dim Dic As Object
[/COLOR]
Private Sub ComboBox1_Click()
   Me.ComboBox2.Clear
   Me.ComboBox3.Clear
   Me.ComboBox2.List = Dic(Me.ComboBox1.Value).keys
End Sub


Private Sub ComboBox2_Click()
   Me.ComboBox3.Clear
   Me.ComboBox3.List = Dic(Me.ComboBox1.Value)(Me.ComboBox2.Value).keys
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("B2", .Range("B" & 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.ComboBox1.List = Dic.keys
End Sub

The lines in red need to go at the very top of the module, before any code.

 

[My Aswer Is This]

I think this is what you need.
I set the sheet names to Movies an Music

Private Sub cboCategory_Change()
'Modified  8/21/2018  8:28:22 AM  EDT
Dim c As Long
Dim SearchString As String
Dim SearchRange As Range
If cboCategory.Value <> "" Then
SearchString = cboCategory.Value
Dim Lastrow As Long
Set SearchRange = Sheets(cboSelect.Value).Cells(1, 1).Resize(, 4).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
c = SearchRange.Column
Lastrow = Sheets(cboSelect.Value).Cells(Rows.Count, c).End(xlUp).Row
If SearchRange Is Nothing Then MsgBox SearchString & "  Not Found": Exit Sub
cboName.List = Sheets(cboSelect.Value).Cells(2, c).Resize(Lastrow).Value
End If
End Sub
Private Sub cboSelect_Change()
cboCategory.Clear
    For i = 1 To 4
        cboCategory.AddItem Sheets(cboSelect.Value).Cells(1, i).Value
    Next
End Sub
Private Sub UserForm_Initialize()
cboSelect.AddItem "Movies"
cboSelect.AddItem "Music"
End Sub