récupération de données dans un userform?

[Mathou]

Bonjour à tous,
J'ai créé un userform ou je souhaiterai en sélectionnant le contenu d'une cellule, récupérer toutes les données de la même dans des textBox, mais rien ne fonctionne.
Mon fichier faisant plus que 50Ko je ne peux vous le joindre, je vous joint donc le code que j'ai tapé, pouvez m'indiquer les erreurs commises?

Merci de votre aide.

Option Explicit
Dim Personnel1 As Range, Tableau1 As Range


Private Sub CmdImprimer_Click()

End Sub

Private Sub CmdSupprimer_Click()

End Sub

Private Sub ComboBoxNOM_Change()
Dim Lgn As Integer
Dim Célibataire, Marié, PACSE, Divorcé As String

Lgn = ComboBoxNOM.ListIndex + 2
With Tableau1
TextDate.Value = Cells(Lgn, 1)
TextPrénom.Value = Cells(Lgn, 3)
TextAdresse1.Value = Cells(Lgn, 4)
TextAdresse2.Value = Cells(Lgn, 5)
TextCodePostal.Value = Cells(Lgn, 6)
TextVille.Value = Cells(Lgn, 7)
TextFixe.Value = Cells(Lgn, 8)
TextPortable.Value = Cells(Lgn, 9)
TextEmail.Value = Cells(Lgn, 10)
TextNiveau_scolaire1.Value = Cells(Lgn, 11)
TextNiveau_scolaire2.Value = Cells(Lgn, 12)
TextNSécu.Value = Cells(Lgn, 13)
ComboBoxGS.Value = Cells(Lgn, 14)
TextRenseignements_complémentaires.Value = Cells(Lgn, 15)
ComboBoxGrade.Value = Cells(Lgn, 16)
TextBox1.Value = Cells(Lgn, 17)
TextBox2.Value = Cells(Lgn, 18)
TextBox3.Value = Cells(Lgn, 19)
TextN_AFPS.Value = Cells(Lgn, 20)
TextBox6.Value = Cells(Lgn, 21)
TextN_CFAPSE.Value = Cells(Lgn, 22)
TextBox5.Value = Cells(Lgn, 23)
TextN_CFAPSR.Value = Cells(Lgn, 24)
TextBox4.Value = Cells(Lgn, 25)
TextCOD.Value = Cells(Lgn, 26)
TextFDF.Value = Cells(Lgn, 27)
TextRCH.Value = Cells(Lgn, 28)
TextRAD.Value = Cells(Lgn, 29)
TextSAV.Value = Cells(Lgn, 30)
TextSAL.Value = Cells(Lgn, 31)
TextIMP.Value = Cells(Lgn, 32)
TextISS.Value = Cells(Lgn, 33)
TextEPS.Value = Cells(Lgn, 34)
TextPRV.Value = Cells(Lgn, 35)
TextPRS.Value = Cells(Lgn, 36)
TextFOR.Value = Cells(Lgn, 37)
TextCAN.Value = Cells(Lgn, 38)
TextCYN.Value = Cells(Lgn, 39)
TextSDE.Value = Cells(Lgn, 40)
TextSMO.Value = Cells(Lgn, 41)
TextTRS.Value = Cells(Lgn, 42)
TextN_Permis_PL.Value = Cells(Lgn, 43)
TextBox7.Value = Cells(Lgn, 44)
'statut = Cells(Lgn, 45)
'situation = Cells(Lgn, 46)
'permis = Cells(Lgn, 47)
'fonction = Cells(Lgn, 48)
'permis_poids_lourds = Cells(Lgn, 49)
End With

If Cells(Lgn, 46).Value = 'Célibataire' Then
OptionButton1 = True
OptionButton2 = False
OptionButton3 = False
OptionButton4 = False
Else
End If
If Cells(Lgn, 46).Value = 'Marié' Then
OptionButton1 = False
OptionButton2 = True
OptionButton3 = False
OptionButton4 = False
Else
End If
If Cells(Lgn, 46).Value = 'PACSE' Then
OptionButton1 = False
OptionButton2 = False
OptionButton3 = True
OptionButton4 = False
Else
End If
If Cells(Lgn, 46).Value = 'Divorcé' Then
OptionButton1 = False
OptionButton2 = False
OptionButton3 = False
OptionButton4 = True

End If



End Sub

Private Sub CommandButton1_Click()
Unload Me
UsfNouveau_Renseignement.Show
End Sub

Private Sub CommandButton2_Click()

Dim Lgn As Integer

Lgn = ComboBoxNOM.ListIndex + 2
If Lgn = 0 Then Exit Sub

With Tableau1
.Cells(Lgn, 1) = TextDate
.Cells(Lgn, 3) = TextPrénom
.Cells(Lgn, 4) = TextAdresse1
.Cells(Lgn, 5) = TextAdresse2
.Cells(Lgn, 6) = TextCodePostal
.Cells(Lgn, 7) = TextVille
.Cells(Lgn, 8) = TextFixe
.Cells(Lgn, 9) = TextPortable
.Cells(Lgn, 10) = TextEmail
.Cells(Lgn, 11) = TextNiveau_scolaire1
.Cells(Lgn, 12) = TextNiveau_scolaire2
.Cells(Lgn, 13) = TextNSécu
.Cells(Lgn, 14) = ComboBoxGS
.Cells(Lgn, 15) = TextRenseignements_complémentaires
.Cells(Lgn, 16) = ComboBoxGrade
.Cells(Lgn, 17) = TextBox1
.Cells(Lgn, 18) = TextBox2
.Cells(Lgn, 19) = TextBox3
.Cells(Lgn, 20) = TextN_AFPS
.Cells(Lgn, 21) = TextBox6
.Cells(Lgn, 22) = TextN_CFAPSE
.Cells(Lgn, 23) = TextBox5
.Cells(Lgn, 24) = TextN_CFAPSR
.Cells(Lgn, 25) = TextBox4
.Cells(Lgn, 26) = TextCOD
.Cells(Lgn, 27) = TextFDF
.Cells(Lgn, 28) = TextRCH
.Cells(Lgn, 29) = TextRAD
.Cells(Lgn, 30) = TextSAV
.Cells(Lgn, 31) = TextSAL
.Cells(Lgn, 32) = TextIMP
.Cells(Lgn, 33) = TextISS
.Cells(Lgn, 34) = TextEPS
.Cells(Lgn, 35) = TextPRV
.Cells(Lgn, 36) = TextPRS
.Cells(Lgn, 37) = TextFOR
.Cells(Lgn, 38) = TextCAN
.Cells(Lgn, 39) = TextCYN
.Cells(Lgn, 40) = TextSDE
.Cells(Lgn, 41) = TextSMO
.Cells(Lgn, 42) = TextTRS
.Cells(Lgn, 43) = TextN_Permis_PL
.Cells(Lgn, 44) = TextBox7
' .Cells(Lgn, 45) = statut
' .Cells(Lgn, 46) = situation
' .Cells(Lgn, 47) = permis
' .Cells(Lgn, 48) = fonction
'.Cells(Lgn, 49) = permis_poids_lourds



End With

With Worksheets('FeuilPersonnel').Select
Range('B2').Select
Unload Me
End With
End Sub


Private Sub TextBox1_Change()

End Sub

Private Sub UserForm_Initialize()

Dim Plage As Range

With Worksheets('FeuilPersonnel')
Set Personnel1 = .Range('B2')
Set Plage = .Range(Personnel1, .Range('B65536').End(xlUp))
Set Tableau1 = Plage.Resize(, 50)
End With

ComboBoxNOM.List = Plage.Value

End Sub