Clic droit=valeur définie en fonction de critères

Private Sub UserForm_Initialize()
Dim DerLi As Long
Dim tableau()
With Sheets("Feuil2")
  tableau = .Range("A2:A8").Value
  Call tri(tableau, 1, UBound(tableau))
  ComboBox1.List = tableau
'    DerLi = .[A65000].End(xlUp).Row
'    For Each cell In .Range("A2:A8")
'      ComboBox1.AddItem cell.Value
'Next
  tableau = .Range("B2:B119").Value
  Call tri(tableau, 1, UBound(tableau))
  ComboBox2.List = tableau
'     For Each cell In .Range("B2:B119")
'      ComboBox2.AddItem cell.Value
'Next
  tableau = .Range("C2:C20").Value
  Call tri(tableau, 1, UBound(tableau))
  ComboBox5.List = tableau
'    For Each cell In .Range("C2:C20")
'      ComboBox5.AddItem cell.Value
'Next
  tableau = .Range("D2:D7").Value
  Call tri(tableau, 1, UBound(tableau))
  ComboBox6.List = tableau
'    For Each cell In .Range("D2:D7")
'      ComboBox6.AddItem cell.Value
'Next
End With
End Sub

Sub tri(a(), gauc, droi) ' Quick sort
   ref = a((gauc + droi) \ 2, 1)
   g = gauc: d = droi
   Do
      Do While a(g, 1) < ref: g = g + 1: Loop
      Do While ref < a(d, 1): d = d - 1: Loop
        If g <= d Then
           temp = a(g, 1): a(g, 1) = a(d, 1): a(d, 1) = temp
           g = g + 1: d = d - 1
        End If
    Loop While g <= d
    If g < droi Then Call tri(a, g, droi)
    If gauc < d Then Call tri(a, gauc, d)
End Sub

                             [LIST]
[*]                            Animateur:  Référent:     AE VAC:   AE TIT:   ATSEM..
[*]Accueil matin:              1h              X
[*]Restaurants scolaire:     2h              X                4h          X
[*]Etude surveillée:           1h              X
[*]Accueil soir:                 1h              X
[*]Entretien soir:                                                 3h          X
[*]Permanences:                                                 7h          X
[/LIST]

Private Sub CommandButton1_Click()
Dim DerLig As Long
With Sheets("Feuil1")
  DerLig = .[S65000].End(xlUp).Row + 1
  Select Case ComboBox6.Value
  Case "ANIMATEUR"
    If CheckBox1.Value = True Then .Range("K" & DerLig).Value = 1
    If CheckBox2.Value = True Then .Range("L" & DerLig).Value = 2
    If CheckBox3.Value = True Then .Range("M" & DerLig).Value = 1
    If CheckBox4.Value = True Then .Range("N" & DerLig).Value = 1
  Case "REFERENT"
    If CheckBox1.Value = True Then .Range("K" & DerLig).Value = "X"
    If CheckBox2.Value = True Then .Range("L" & DerLig).Value = "X"
    If CheckBox3.Value = True Then .Range("M" & DerLig).Value = "X"
    If CheckBox4.Value = True Then .Range("N" & DerLig).Value = "X"
  Case "AE VAC"
    If CheckBox2.Value = True Then .Range("L" & DerLig).Value = 4
    If CheckBox5.Value = True Then .Range("O" & DerLig).Value = 3
  Case "AE TIT"
    If CheckBox2.Value = True Then .Range("L" & DerLig).Value = "X"
    If CheckBox5.Value = True Then .Range("O" & DerLig).Value = "X"
  Case "ATSEM"
    If CheckBox2.Value = True Then .Range("L" & DerLig).Value = "X"
    If CheckBox6.Value = True Then .Range("P" & DerLig).Value = "X"
    If CheckBox7.Value = True Then .Range("Q" & DerLig).Value = "X"
    If CheckBox8.Value = True Then .Range("R" & DerLig).Value = "X"
  Case "ATSEM REMPL"
    If CheckBox2.Value = True Then .Range("L" & DerLig).Value = 2
    If CheckBox6.Value = True Then .Range("P" & DerLig).Value = 7
    If CheckBox7.Value = True Then .Range("Q" & DerLig).Value = 4
    If CheckBox8.Value = True Then .Range("R" & DerLig).Value = 3
  Case "PERMANENTE"
    If CheckBox6.Value = True Then .Range("P" & DerLig).Value = 7
    If CheckBox7.Value = True Then .Range("Q" & DerLig).Value = 4
    If CheckBox8.Value = True Then .Range("R" & DerLig).Value = 3
  End Select
  .Range("S" & DerLig).Value = ComboBox6.Value
End With
      
'With Sheets("Feuil1")
'    DerLig = .[A65000].End(xlUp).Row + 1
'    .Cells(DerLig, 1) = Me.ComboBox1
'    .Cells(DerLig, 2) = Me.TextBox1
'    .Cells(DerLig, 4) = IIf(Me.OptionButton2, Me.ComboBox2, "")
'    .Cells(DerLig, 5) = IIf(Me.OptionButton1, Me.ComboBox2, "")
'    .Cells(DerLig, 6) = Date
'    .Cells(DerLig, 8) = Me.DTPicker1
'    .Cells(DerLig, 9) = Me.DTPicker2
'    .Cells(DerLig, 10) = Me.ComboBox5
'    For i = 1 To 8
'        If Me.Controls("Checkbox" & i) Then
'            Cells(DerLig, i + 10) = "X"
'        End If
'    Next i
'    .Cells(DerLig, 19) = Me.ComboBox6
'End With
Unload Me
End Sub