VBA Besoin d'aide

Option Explicit

Private Sub CommandButton1_Click()
'BOUTON VALIDER
Dim trouve As Range
Dim Notes() As Integer, i As Integer
Dim Ctrl As Control

'on teste d'abord si les listes sont bien remplies :
If ComboBox1 = "" Or ComboBox2 = "" Then Exit Sub
'remplissage
'on remplit la variable tableau Notes avec les valeurs des optionbutton
For Each Ctrl In Me.Controls
    If TypeOf Ctrl Is MSForms.OptionButton Then
        If Ctrl.Value = True Then
            ReDim Preserve Notes(i)
            Notes(i) = Ctrl.Caption
            i = i + 1
        End If
    End If
Next
'restitution
'on cherche le contenu de la liste des noms dans la colonne 1 feuil1 :
With Sheets("Feuil1")
    Set trouve = .Columns(1).Cells.Find(ComboBox1)
    If trouve Is Nothing Then
        MsgBox "Impossible n'est pas Français!"
    Else
        For i = 0 To UBound(Notes)
            trouve.Offset(0, 2 + i) = Notes(i)
        Next
    End If
End With
'-------- On "vide" les boutons d'options -----
For Each Ctrl In Me.Controls
    If TypeOf Ctrl Is MSForms.OptionButton Then
        Ctrl.Value = False
    End If
Next
'--------- On remets à zéro les listes sans effacer leur contenu
ComboBox1 = ""
ComboBox2 = ""
End Sub

Private Sub CommandButton2_Click()
'BOUTON FIN
End
End Sub

Private Sub UserForm_Initialize()
'A L'INITIALISATION DE L'USERFORM
Dim Ctrl As Control

'-------- On "vide" les boutons d'options -----
For Each Ctrl In Me.Controls
    If TypeOf Ctrl Is MSForms.OptionButton Then
        Ctrl.Value = False
    End If
Next

'-------- on remplit les listes : --------
    'le 1er 1 pour ComboBox1,
    'le second 1 pour le numéro de colonne utilisé pour le remplissage
    '"Feuil1" est bien sur le nom de la feuille ou sont les données de remplissage
Call RempliMaListe(1, 1, "Feuil1")
    'le 1er 2 pour ComboBox2
    'le second 2 pour le numéro de colonne utilisé pour le remplissage
Call RempliMaListe(2, 2, "Feuil1")
End Sub

Sub RempliMaListe(Indic As Integer, Colonn As Integer, NomFeuil As String)
'PROCEDURE DE REMPLISSAGE DE LISTE
Dim Lig As Long, DrLig As Long

Me.Controls("ComboBox" & Indic).Clear
With Sheets(NomFeuil)
    DrLig = .Columns(Colonn).Find("*", , , , xlByColumns, xlPrevious).Row
    For Lig = 2 To DrLig '2 à modifier si pas de ligne d'entête
        Me.Controls("ComboBox" & Indic).AddItem .Cells(Lig, Colonn)
    Next
End With
End Sub

Private Sub CommandButton1_Click()
Dim trouve As Range
Dim Notes() As Integer, i As Integer
Dim Ctrl As Control
If ComboBox1 = "" Or ComboBox2 = "" Then Exit Sub
Dim test As Boolean
test = False
For Each Ctrl In Me.Controls
    If TypeOf Ctrl Is MSForms.OptionButton Then
        If Ctrl.Value = True Then
            ReDim Preserve Notes(i)
            Notes(i) = Ctrl.Caption
            i = i + 1
            test = True
        End If
    End If
Next
If test = False Then
    MsgBox "Aucun bouton d'option n'a été sélectionné. Arrêt de la procédure."
    Exit Sub
End If
With Sheets("Feuil5")
   Set trouve = .Columns(1).Cells.Find(ComboBox1)
   If trouve Is Nothing Then
       MsgBox "Non correct"
   Else
       For i = 0 To UBount(Notes)
           trouve.Offset(0, 2 + i) = Notes(i)
       Next
   End If
End With
For Each Ctrl In Me.Controls
    If TypeOf Ctrl Is MSForms.OptionButton Then
        Ctrl.Value = False
    End If
Next
ComboBox1 = ""
ComboBox2 = ""
End Sub

Private Sub CommandButton2_Click()
End
End Sub

Private Sub UserForm1_Initialize()
Dim Ctrl As Control
For Each Ctrl In Me.Controls
    If TypeOf Ctrl Is MSForms.OptioButton Then
        Ctrl.Value = False
    End If
Next
Call RempliMaListe(1, 1, "Feuil5")
Call RempliMaListe(2, 2, "Feuil5")
End Sub

Sub RempliMaListe(Indic As Integer, Colonn As Integer, NomFeuil As String)
Dim Lig As Long, DrLig As Long

Me.Controls("ComboBox" & Indic).Clear
With Sheets(NomFeuil)
    DrLig = .Columns(Colonn).Find("*", , , , xlByColumns, xlPrevious).Row
    For Lig = 2 To DrLig
        Me.Controls("ComboBox" & Indic).AddItem .Cells(Lig, Colonn)
    Next
End With
End Sub