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