M
Mathou
Guest
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
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