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]

I'm sorry I think you need this:
During testing I only used 4 column per sheet.
You said 17
So this script using 17 columns per sheet:

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 17
        cboCategory.AddItem Sheets(cboSelect.Value).Cells(1, i).Value
    Next
End Sub
Private Sub UserForm_Initialize()
cboSelect.AddItem "Movies"
cboSelect.AddItem "Music"
End Sub

 

[Gregm66]

trying trying.
in both instances trying both codes seperately.

Runtime Error 70
Permission Denied.

 

[My Aswer Is This]

Well with my script did you change the script or the sheet names.
My script assumes your two sheet names are named Movies and Music

And if you select Movies from Combobox1 and select Love from Combobox2 then the column with Love in row one of sheet Movies and there are no Movies about Love you will get a error.

 

[Gregm66]

ok, thats where the confusion is.
"Movies" & "Music" are my List Names.
My sheet names are "MovieList&Details" and "MusicList"

will try adjusting that part of the code

Again thanks for your help so far

 

[My Aswer Is This]

Yes. I know you confused me.

What do you mean by Listnames

I never understood that. I thought we were dealing with three Cobobox's what does the list have to do with this.

 

[My Aswer Is This]

And should we be looking for categories in column 2 to 17 or 2 to 18

And again explain what this list thing have to do with this project.

 

[Gregm66]

Heres all the code that runs on "Sheet9("SearchActororArtist")

Option Explicit

Private Sub cboSelect_Change()
'cboCategory.Clear
    'For i = 1 To 4
        'cboCategory.AddItem Sheets(cboSelect.Value).Cells(1, i).Value
    'Next
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()
'cboSelect.AddItem "MovieList&Details"
'cboSelect.AddItem "MusicList"
    With Application
  .WindowState = xlMaximized
  Zoom = Int(.Width / Me.Width * 100)
  Width = .Width
  Height = .Height
  End With
End Sub

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("MovieList&Details").Cells(1, 1).Resize(, 4).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
c = SearchRange.Column
Lastrow = Sheets("MovieList&Details").Cells(Rows.Count, c).End(xlUp).Row
If SearchRange Is Nothing Then MsgBox SearchString & "  Not Found": Exit Sub
cboName.List = Sheets("MovieList&Details").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

The first lines that have select case these work and populate my first combobox("cboSelect"), cboselect only shows the list "Movies" and "Music", select 1, the cboCategory will show the corresponding items, IE: select "Movies" and cboCategory will show Action, History, Drama etc etc. these 2 comboboxes are functioning with my original code.
If i were to choose "Action" from cboCategory then cboName needs to filter through all the movies on sheet "MovieList&Details" and only return the movies that are action Movies. Movie name is in Column B, Movie Type IE: action etc is in Column "C".

I remmed out some lines of code as you can see, it loaded my first 2 lists, but when i got to "cboName", it errored at line,

Set SearchRange = Sheets("MovieList&Details").Cells(1, 1).Resize(, 4).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
c = SearchRange.Column

Any Ideas.

 

[Gregm66]

Ok, "cboSelect" has to load to words, "Movies and Music', these 2 words are my list. so in the propertys for the "cboSelect" combobox i have set row source to Category, that is the list name for those 2 words.

Then my code looks at "cboSelect" and then chooses between 2 more lists, one named "Movies" the other named "Music"
i hope this is easy to understand so far. sorry for the confusion.

 

[My Aswer Is This]

I thought

cboSelect​

were sheet names in your Combobox named CboSelet

And you selected one or the other.

And I do not know why your using RowSource=Category

What is category?


And you still keep using the term: Lists
you said:

and then chooses between 2 more lists

I thought we were dealing with comboboxes not lists
Or do you mean the list of values in the combobox

Are you taking about Listbox​

 

[Gregm66]

no i do not have any listboxs.
this all started out using worksheets to do the job. hence why i have lists. i found that the easy way to populate my first combobox "cboSelect" was to use row source and refer it to i will change my wording, refer to a named range called "Category" this shows the 2 words "Movies & Music"

I will try to stop calling them lists, bad habit of mine.