vbaif-statementlistboxuserformselect-case

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


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.


Solution

  • 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.