Two dependent comboboxes [Closed]

Posts
1
Registration date
Monday November 20, 2017
Last seen
November 20, 2017
-
Private Sub UserForm_Initialize()
ComboBox1.Clear
ComboBox1.List = Array("PET-Course", "PET-Lab", "PET-OCT", "ENG-Course", "HUM-Course")
ComboBox2.Clear
ComboBox3.Clear

End Sub
-------------------------------------------------------------
Private Sub ComboBox1_Change()

'Avoid the bug generated when a user deleted the content of ComboBox1)'
If ComboBox1.Value = "" Then Exit Sub
ComboBox2.Clear
ComboBox3.Clear
ComboBox2.List = Array("PET-C", "PET-L", "PET-O", "ENG", "HUM")

' (select only appropraite to the ComboBox 1 selection course code.e.g PET-Course=PET, ENG-course=ENG,PET-Lab=Lab)'
Dim index As Integer
index = ComboBox1.ListIndex

ComboBox2.Clear

Select Case index
Case Is = 0

With ComboBox2
.AddItem "PET-C"


End With
Case Is = 1
With ComboBox2
.AddItem "PET-L"

End With

End Select
End Sub
---------------------------------------------------
Private Sub ComboBox2_Change()
If ComboBox2.Value = "" Then Exit Sub
ComboBox3.Clear
ComboBox4.Clear
ComboBox2.List = Array("PET-C", "PET-L", )

Dim index1 As Integer
index1 = ComboBox2.ListIndex

ComboBox3.Clear
Select Case index1
Case Is = 0
With ComboBox3
.AddItem "110"
.AddItem "120"
End With

With ComboBox3
.AddItem "111"
.AddItem "110"
End With

End Select
End Sub

--------------------------
Dear CCM.net VBA comminity
I'm new to VBA and since would be much appreciated of the help on the following:
1) the code above is presented whci combines 3 comboboxes in VBA (no Excel involved sheets involved)
2) my questions are:
how can I introduce the list of itmeswithout going through "Select Case" method
since my data is quite multiple in linking Comboboxes between each other will be time inefficient. smth like a "For i Loop" option?
3) I have also Combobox4 which should show unique value based on Comboboxes 2&3 values -so, how to "link them all"?
4) and the last question (minor one) -how to "activate " userfrom, so that whene Excel file is opneded it will pop up automatically?

so far what I have browsed is with the use of spreadsheet - but I don't want to do this..
thanks a lot in advance!
See more