Private Sub ComboBox1_Change()
CommandButton1.Enabled = ComboBox1.ListIndex > -1 And ComboBox2.ListIndex > -1
End Sub
Private Sub ComboBox2_Change()
'Autorise le bouton ok uniquemment si les 2 combobox ont une sélection
CommandButton1.Enabled = ComboBox1.ListIndex > -1 And ComboBox2.ListIndex > -1
End Sub
Private Sub UserForm_Initialize()
Dim sh As Worksheet
Dim Niveau As String
For Each sh In ThisWorkbook.Worksheets
If sh.Name Like "Niveau*" Then
Niveau = Left(sh.Name, InStr(1, sh.Name, "Séance") - 2)
ComboBox1.Text = Niveau
If ComboBox1.ListIndex = -1 Then ComboBox1.AddItem Niveau
End If
Next
'Premier élément de liste pas défaut
If ComboBox1.ListCount > 0 Then ComboBox1.ListIndex = 0
'CommandButton1.Enabled = ComboBox1.ListIndex > -1 And ComboBox2.ListIndex > -1
End Sub
Private Sub ComboBox1_Click()
Dim sh As Worksheet
Dim Séance As String, tmp As String
Dim i As Integer
If ComboBox1.ListIndex > -1 Then
ComboBox2.Clear
For Each sh In ThisWorkbook.Worksheets
If sh.Name Like ComboBox1.Value & "*" Then
'Extraction de la séance dans le nom
Séance = Trim(Right(sh.Name, Len(sh.Name) - Len(ComboBox1.Value)))
'
ComboBox2.Text = Séance
'En changeant la propriété .Text du combobox si l'item exite déjà .listIndex sera >-1
If ComboBox2.ListIndex = -1 Then
If ComboBox2.ListCount > 0 Then
'Boucle à la recherche de la bonne position
'déterminée par la valeur du numéro de séance
For i = 0 To ComboBox2.ListCount - 1
If Split(ComboBox2.List(i), " ")(1) > Split(Séance, " ")(1) Then Exit For
Next
End If
ComboBox2.AddItem Séance, i
End If
End If
Next
End If
'Premier élément de liste par défaut
If ComboBox2.ListCount > 0 Then ComboBox2.ListIndex = 0
End Sub
Private Sub CommandButton1_Click()
Dim sh As Worksheet
'Parcourt les feuilles, cachent celles dont le nom de correspond pas aux combobox et affiche celle qui correspond
For Each sh In ThisWorkbook.Worksheets
If sh.Name Like "Niveau*" Then
sh.Visible = sh.Name = Trim(ComboBox1.Value) & " " & Trim(ComboBox2.Value)
If sh.Visible Then sh.Activate
End If
Next
Unload UserForm1
End Sub
Private Sub CommandButton2_Click()
Unload UserForm1
End Sub