Generate options for 3rd ListBox based on 2nd ListBox selection

Question

I wrote a simple version of a userform to pinpoint my issue.

The selection of ListBox1 lstClassName determines the options for ListBox2 lstClassName (working).

The selection for ListBox2 is supposed to determine the options for ListBox3 lstLanguage (not working).

The answers to similar questions involve more than I need this userform to do.

I uploaded the file to Google Drive. You can see how the form is intended to work. Link To Excel File

Option Explicit
Public ClassX As Integer
Public LanguageX As Integer

Private Sub UserForm_Initialize()
    With lstClassName
        .AddItem "Cooking"
        .AddItem "Art"
        .AddItem "Music"
    End With
End Sub
    
    
Private Sub lstClassName_Click()
    ClassX = lstClassName.ListIndex
    Select Case ClassX
    Case Is = 0 'Cooking Class
        lstLanguage.Clear
        lstLanguage.AddItem "English"
        lstLanguage.AddItem "Spanish"
     Case Is = 1 'Art Class
        lstLanguage.Clear
        lstLanguage.AddItem "English"
        lstLanguage.AddItem "French"
    Case Is = 2 'Music Class
        lstLanguage.Clear
        lstLanguage.AddItem "English"
        lstLanguage.AddItem "Spanish"
        lstLanguage.AddItem "French"
    End Select
End Sub
    
Private Sub lstLanguage_Click()
    LanguageX = lstLanguage.ListIndex
    Select Case LanguageX
    Case (ClassX = 0 And LanguageX = 0) 'Cooking Class in English
        lstDay.Clear
        lstDay.AddItem "Monday"
        lstDay.AddItem "Wednesday"
    Case (ClassX = 0 And LanguageX = 1) 'Cooking Class in Spanish
        lstDay.Clear
        lstDay.AddItem "Monday"
        lstDay.AddItem "Thursday"
    Case (ClassX = 1 And LanguageX = 0) 'Art Class in English
        lstDay.Clear
        lstDay.AddItem "Tuesday"
        lstDay.AddItem "Friday"
    Case (ClassX = 1 And LanguageX = 1) 'Art Class in French
        lstDay.Clear
        lstDay.AddItem "Wednesday"
        lstDay.AddItem "Thursday"
    Case (ClassX = 2 And LanguageX = 0) 'Music Class in English
        lstDay.Clear
        lstDay.AddItem "Monday"
        lstDay.AddItem "Friday"
    Case (ClassX = 2 And LanguageX = 1) 'Music Class in Spanish
        lstDay.Clear
        lstDay.AddItem "Tuesday"
        lstDay.AddItem "Wednesday"
    Case (ClassX = 2 And LanguageX = 2) 'Music Class in French
        lstDay.Clear
        lstDay.AddItem "Thursday"
        lstDay.AddItem "Friday"
    End Select
End Sub
    
Private Sub CommandButton1_Click()
    'This would then be the button to add data to a part of the spreadsheet
End Sub

Note: I didn't bother finishing the code for the CommandButton or what to do with the data from this userform.

PatricK · Accepted Answer · 2018-05-17T22:44:53.877

A creative alternative for the lstLanguage_Click() here. Since you only have 2 variables, you can convert them into a decimal value for easier coding. ClassX will be the integer part and LanguageX will be the decimal part.

Private Sub lstLanguage_Click()
    Dim uCode As Double
    LanguageX = lstLanguage.ListIndex
    uCode = CDbl(ClassX) + CDbl(LanguageX) / 10
    lstDay.Clear ' This is done for any one clicked
    Select Case uCode
        Case 0# ' (ClassX = 0 And LanguageX = 0) 'Cooking Class in English
            lstDay.AddItem "Monday"
            lstDay.AddItem "Wednesday"
        Case 0.1 ' (ClassX = 0 And LanguageX = 1) 'Cooking Class in Spanish
            lstDay.AddItem "Monday"
            lstDay.AddItem "Thursday"
        Case 1#  ' (ClassX = 1 And LanguageX = 0) 'Art Class in English
            lstDay.AddItem "Tuesday"
            lstDay.AddItem "Friday"
        Case 1.1 ' (ClassX = 1 And LanguageX = 1) 'Art Class in French
            lstDay.AddItem "Wednesday"
            lstDay.AddItem "Thursday"
        Case 2#  ' (ClassX = 2 And LanguageX = 0) 'Music Class in English
            lstDay.AddItem "Monday"
            lstDay.AddItem "Friday"
        Case 2.1 ' (ClassX = 2 And LanguageX = 1) 'Music Class in Spanish
            lstDay.AddItem "Tuesday"
            lstDay.AddItem "Wednesday"
        Case 2.2 ' (ClassX = 2 And LanguageX = 2) 'Music Class in French
            lstDay.AddItem "Thursday"
            lstDay.AddItem "Friday"
    End Select
End Sub

If you prefer stick to the way you are using, the fix is simple.
Change Select Case LanguageX to Select Case True.

Yes, changing Select Case LanguageX to Select Case True fixed the issue. Many thanks! — SSG, May 17 '18 at 14:40
Also thanks for the creative alternative you posted. As a beginner I appreciate being shown examples like that. — SSG, May 17 '18 at 14:49

score 0 · Answer 2 · answered May 16 '18 at 23:14

It's got something to do with the Case statement. Don't know exactly what because I don't use it that often but if you have multiple conditions you are usually better of with If. Also consider changing the click events to change events. Using this code should work.

Option Explicit
Public ClassX As Integer
Public LanguageX As Integer



Private Sub UserForm_Initialize()

With lstClassName
        .AddItem "Cooking"
        .AddItem "Art"
        .AddItem "Music"
End With


End Sub


Private Sub lstClassName_Click()

ClassX = lstClassName.ListIndex

Select Case ClassX
    Case Is = 0 'Cooking Class
        lstLanguage.Clear
        lstLanguage.AddItem "English"
        lstLanguage.AddItem "Spanish"

    Case Is = 1 'Art Class
        lstLanguage.Clear
        lstLanguage.AddItem "English"
        lstLanguage.AddItem "French"

    Case Is = 2 'Music Class
        lstLanguage.Clear
        lstLanguage.AddItem "English"
        lstLanguage.AddItem "Spanish"
        lstLanguage.AddItem "French"
End Select

lstDay.Clear


End Sub

Private Sub lstLanguage_Click()

LanguageX = lstLanguage.ListIndex

If ClassX = 0 And LanguageX = 0 Then 'Cooking Class in English
        lstDay.Clear
        lstDay.AddItem "Monday"
        lstDay.AddItem "Wednesday"
    ElseIf ClassX = 0 And LanguageX = 1 Then 'Cooking Class in Spanish
        lstDay.Clear
        lstDay.AddItem "Monday"
        lstDay.AddItem "Thursday"
    ElseIf ClassX = 1 And LanguageX = 0 Then 'Art Class in English
        lstDay.Clear
        lstDay.AddItem "Tuesday"
        lstDay.AddItem "Friday"
    ElseIf ClassX = 1 And LanguageX = 1 Then 'Art Class in French
        lstDay.Clear
        lstDay.AddItem "Wednesday"
        lstDay.AddItem "Thursday"
    ElseIf ClassX = 2 And LanguageX = 0 Then 'Music Class in English
        lstDay.Clear
        lstDay.AddItem "Monday"
        lstDay.AddItem "Friday"
    ElseIf ClassX = 2 And LanguageX = 1 Then 'Music Class in Spanish
        lstDay.Clear
        lstDay.AddItem "Tuesday"
        lstDay.AddItem "Wednesday"
    ElseIf ClassX = 2 And LanguageX = 2 Then 'Music Class in French
        lstDay.Clear
        lstDay.AddItem "Thursday"
        lstDay.AddItem "Friday"
End If


End Sub



Private Sub CommandButton1_Click()

End Sub

Yes, this worked! Thanks so much. I will keep the If Statement in mind as an option. — SSG, May 17 '18 at 15:01

score 0 · Answer 3 · answered Jan 19 '21 at 22:01

You can provide faster solutions by using the Dictionary object. You can provide faster solutions by using the Dictionary object. First, dynamic field names are created for the columns. 3 listboxes are added to the userform, codes are added for the dependent listboxes .

Source codes and sample workbook here

Generate options for 3rd ListBox based on 2nd ListBox selection

3 Answers3