jeudi 17 mai 2018

VBA Userform w/ Multiple Dependent Listboxes - Trouble getting 3rd Box to read 2nd Box selection (using Select Case)

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

The selection of ListBox1 lstClassName determines the options for ListBox2 lstClassName (which is working), and the selection for ListBox2 is supposed to determine the options for ListBox3 lstLanguage (which is not working).

All of the answers I've found to other similar questions involve more than I need this userform to do. And since I'm pretty new to VBA, I would appreciate some help understanding what I'm doing wrong.

I've uploaded the file to Google Drive so you can see how the form is intended to work. Link To Excel File

Here is the code:

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

Thanks!

Aucun commentaire:

Enregistrer un commentaire