Djibysadji
XLDnaute Nouveau
Salut la communauté!
Je sollicite encore votre soutien.
Mon projet est de concevoir un formulaire où je pourrais insérer du texte et des photos à partir d'une base de données et les faire défiler à l'aide d'une comboBox alimentée ou avec les boutons suivant et précédent.
J'ai presque réussi car je parviens à faire défiler les données texte. Le seul hic est qu'il n'y a que la photo par défaut qui vient à la place des personnes dont les info s'affichent dans les textBox.
Pour y voir clair et vous permettre de m'aider plus facilement, je joins ci-dessous une base de données vidée de ses info, mais mon Userform et le code effectué.
Option Explicit
Dim i As Integer, lr As Integer
Dim CurrentRow As Long
Private Sub cmdModifier_Click()
i = Me.ComboBox1.ListIndex + 2
If Me.ComboBox1.Value = "" Then
MsgBox "veuillez sélectionner une donnée dans la liste déroulante"
Else
Cells(i, 1).Value = Me.TextBox1.Value
Cells(i, 2).Value = Me.TextBox2.Value
Cells(i, 3).Value = Me.TextBox3.Value
Cells(i, 4).Value = Me.TextBox4.Value
Cells(i, 5).Value = Me.TextBox5.Value
Cells(i, 6).Value = Me.TextBox6.Value
Cells(i, 7).Value = Me.TextBox7.Value
Cells(i, 8).Value = Me.TextBox8.Value
Cells(i, 9).Value = Me.TextBox9.Value
Cells(i, 10).Value = Me.TextBox10.Value
Cells(i, 11).Value = Me.TextBox11.Value
Cells(i, 12).Value = Me.TextBox12.Value
Cells(i, 13).Value = Me.TextBox13.Value
Cells(i, 14).Value = Me.TextBox14.Value
Cells(i, 15).Value = Me.TextBox15.Value
Cells(i, 16).Value = Me.TextBox16.Value
Cells(i, 17).Value = Me.TextBox17.Value
Cells(i, 18).Value = Me.TextBox18.Value
Cells(i, 19).Value = Me.TextBox19.Value
Cells(i, 20).Value = Me.TextBox20.Value
End If
If Me.CheckBox1 = True Then
Cells(i, 21).Value = "oui"
Else
Cells(i, 21).Value = "oui"
End If
End Sub
Private Sub CmdPrécédent_Click()
CurrentRow = CurrentRow - 1
If CurrentRow > 1 Then
TextBox1.Text = Cells(CurrentRow, 1).Value
TextBox2.Text = Cells(CurrentRow, 2).Value
TextBox3.Text = Cells(CurrentRow, 3).Value
TextBox4.Text = Cells(CurrentRow, 4).Value
TextBox5.Text = Cells(CurrentRow, 5).Value
TextBox6.Text = Cells(CurrentRow, 6).Value
TextBox7.Text = Cells(CurrentRow, 7).Value
TextBox8.Text = Cells(CurrentRow, 8).Value
TextBox9.Text = Cells(CurrentRow, 9).Value
TextBox10.Text = Cells(CurrentRow, 10).Value
TextBox11.Text = Cells(CurrentRow, 11).Value
TextBox12.Text = Cells(CurrentRow, 12).Value
TextBox13.Text = Cells(CurrentRow, 13).Value
TextBox14.Text = Cells(CurrentRow, 14).Value
TextBox15.Text = Cells(CurrentRow, 15).Value
TextBox16.Text = Cells(CurrentRow, 16).Value
TextBox17.Text = Cells(CurrentRow, 17).Value
TextBox18.Text = Cells(CurrentRow, 18).Value
TextBox19.Text = Cells(CurrentRow, 19).Value
TextBox20.Text = Cells(CurrentRow, 20).Value
ElseIf CurrentRow = 1 Then
CurrentRow = CurrentRow + 1
MsgBox "Vous êtes au premier enregistrement"
End If
End Sub
Private Sub CmdQuitter_Click()
Unload Me
End Sub
Private Sub cmdSuivant_Click()
lr = Sheets(1).Range("A100").End(xlUp).Row
CurrentRow = CurrentRow + 1
If CurrentRow = lr + 1 Then
CurrentRow = lr
MsgBox "Vous êtes au dernier enregistrement"
End If
TextBox1.Text = Cells(CurrentRow, 1).Value
TextBox2.Text = Cells(CurrentRow, 2).Value
TextBox3.Text = Cells(CurrentRow, 3).Value
TextBox4.Text = Cells(CurrentRow, 4).Value
TextBox5.Text = Cells(CurrentRow, 5).Value
TextBox6.Text = Cells(CurrentRow, 6).Value
TextBox7.Text = Cells(CurrentRow, 7).Value
TextBox8.Text = Cells(CurrentRow, 8).Value
TextBox9.Text = Cells(CurrentRow, 9).Value
TextBox10.Text = Cells(CurrentRow, 10).Value
TextBox11.Text = Cells(CurrentRow, 11).Value
TextBox12.Text = Cells(CurrentRow, 12).Value
TextBox13.Text = Cells(CurrentRow, 13).Value
TextBox14.Text = Cells(CurrentRow, 14).Value
TextBox15.Text = Cells(CurrentRow, 15).Value
TextBox16.Text = Cells(CurrentRow, 16).Value
TextBox17.Text = Cells(CurrentRow, 17).Value
TextBox18.Text = Cells(CurrentRow, 18).Value
TextBox19.Text = Cells(CurrentRow, 19).Value
TextBox20.Text = Cells(CurrentRow, 20).Value
End Sub
Private Sub CmdValider_Click()
If MsgBox("Validez-vous ces données?", vbYesNo, "Validation") = vbYes Then
lr = Sheets(1).Range("A100").End(xlUp).Row + 1
Cells(lr, 1).Value = Me.TextBox1
Cells(lr, 2).Value = Me.TextBox2
Cells(lr, 3).Value = Me.TextBox3
Cells(lr, 4).Value = Me.TextBox4
Cells(lr, 5).Value = Me.TextBox5
Cells(lr, 6).Value = Me.TextBox6
Cells(lr, 7).Value = Me.TextBox7
Cells(lr, 8).Value = Me.TextBox8
Cells(lr, 9).Value = Me.TextBox9
Cells(lr, 10).Value = Me.TextBox10
Cells(lr, 11).Value = Me.TextBox11
Cells(lr, 12).Value = Me.TextBox12
Cells(lr, 13).Value = Me.TextBox13
Cells(lr, 14).Value = Me.TextBox14
Cells(lr, 15).Value = Me.TextBox15
Cells(lr, 16).Value = Me.TextBox16
Cells(lr, 17).Value = Me.TextBox17
Cells(lr, 18).Value = Me.TextBox18
Cells(lr, 19).Value = Me.TextBox19
Cells(lr, 20).Value = Me.TextBox20
If Me.CheckBox1 = True Then
Cells(lr, 21).Value = "oui"
Else
Cells(lr, 21).Value = "non"
End If
End If
Range("a2").Select
Me.TextBox1 = ""
Me.TextBox2 = ""
Me.TextBox3 = ""
Me.TextBox4 = ""
Me.TextBox5 = ""
Me.TextBox6 = ""
Me.TextBox7 = ""
Me.TextBox8 = ""
Me.TextBox9 = ""
Me.TextBox10 = ""
Me.TextBox11 = ""
Me.TextBox12 = ""
Me.TextBox13 = ""
Me.TextBox14 = ""
Me.TextBox15 = ""
Me.TextBox16 = ""
Me.TextBox17 = ""
Me.TextBox18 = ""
Me.TextBox19 = ""
Me.TextBox20 = ""
End Sub
Private Sub ComboBox1_Change()
Dim Photo As String
i = Me.ComboBox1.ListIndex + 2
Me.TextBox1.Text = Cells(i, 1).Value
Me.TextBox2.Text = Cells(i, 2).Value
Me.TextBox3.Text = Cells(i, 3).Value
Me.TextBox4.Text = Cells(i, 4).Value
Me.TextBox5.Text = Cells(i, 5).Value
Me.TextBox6.Text = Cells(i, 6).Value
Me.TextBox7.Text = Cells(i, 7).Value
Me.TextBox8.Text = Cells(i, 8).Value
Me.TextBox9.Text = Cells(i, 9).Value
Me.TextBox10.Text = Cells(i, 10).Value
Me.TextBox11.Text = Cells(i, 11).Value
Me.TextBox12.Text = Cells(i, 12).Value
Me.TextBox13.Text = Cells(i, 13).Value
Me.TextBox14.Text = Cells(i, 14).Value
Me.TextBox15.Text = Cells(i, 15).Value
Me.TextBox16.Text = Cells(i, 16).Value
Me.TextBox17.Text = Cells(i, 17).Value
Me.TextBox18.Text = Cells(i, 18).Value
Me.TextBox19.Text = Cells(i, 19).Value
Me.TextBox20.Text = Cells(i, 20).Value
On Error GoTo defaut
Photo = ComboBox1.Value
Image1.Picture = LoadPicture("C:\Users\HP\OneDrive\Bureau\Photopersonnels" & Photo & ".jpg")
Exit Sub
defaut:
Image1.Picture = LoadPicture("C:\Users\HP\OneDrive\Bureau\Photopersonnels\Default.jpg")
End Sub
Private Sub TextBox1_Change()
On Error GoTo defaut
Dim Photo As String
Photo = TextBox1.Value
Image1.Picture = LoadPicture("C:\Users\HP\OneDrive\Bureau\Photopersonnels\" & Photo & ".jpg")
Exit Sub
defaut:
Image1.Picture = LoadPicture("C:\Users\HP\OneDrive\Bureau\Photopersonnels\Default.jpg")
End Sub
Private Sub UserForm_Initialize()
CurrentRow = 1
lr = Sheets(1).Range("A100").End(xlUp).Row
For i = 2 To lr
ComboBox1.AddItem Sheets("SOURCE").Cells(i, 1)
Next i
End Sub
Je sollicite encore votre soutien.
Mon projet est de concevoir un formulaire où je pourrais insérer du texte et des photos à partir d'une base de données et les faire défiler à l'aide d'une comboBox alimentée ou avec les boutons suivant et précédent.
J'ai presque réussi car je parviens à faire défiler les données texte. Le seul hic est qu'il n'y a que la photo par défaut qui vient à la place des personnes dont les info s'affichent dans les textBox.
Pour y voir clair et vous permettre de m'aider plus facilement, je joins ci-dessous une base de données vidée de ses info, mais mon Userform et le code effectué.
Option Explicit
Dim i As Integer, lr As Integer
Dim CurrentRow As Long
Private Sub cmdModifier_Click()
i = Me.ComboBox1.ListIndex + 2
If Me.ComboBox1.Value = "" Then
MsgBox "veuillez sélectionner une donnée dans la liste déroulante"
Else
Cells(i, 1).Value = Me.TextBox1.Value
Cells(i, 2).Value = Me.TextBox2.Value
Cells(i, 3).Value = Me.TextBox3.Value
Cells(i, 4).Value = Me.TextBox4.Value
Cells(i, 5).Value = Me.TextBox5.Value
Cells(i, 6).Value = Me.TextBox6.Value
Cells(i, 7).Value = Me.TextBox7.Value
Cells(i, 8).Value = Me.TextBox8.Value
Cells(i, 9).Value = Me.TextBox9.Value
Cells(i, 10).Value = Me.TextBox10.Value
Cells(i, 11).Value = Me.TextBox11.Value
Cells(i, 12).Value = Me.TextBox12.Value
Cells(i, 13).Value = Me.TextBox13.Value
Cells(i, 14).Value = Me.TextBox14.Value
Cells(i, 15).Value = Me.TextBox15.Value
Cells(i, 16).Value = Me.TextBox16.Value
Cells(i, 17).Value = Me.TextBox17.Value
Cells(i, 18).Value = Me.TextBox18.Value
Cells(i, 19).Value = Me.TextBox19.Value
Cells(i, 20).Value = Me.TextBox20.Value
End If
If Me.CheckBox1 = True Then
Cells(i, 21).Value = "oui"
Else
Cells(i, 21).Value = "oui"
End If
End Sub
Private Sub CmdPrécédent_Click()
CurrentRow = CurrentRow - 1
If CurrentRow > 1 Then
TextBox1.Text = Cells(CurrentRow, 1).Value
TextBox2.Text = Cells(CurrentRow, 2).Value
TextBox3.Text = Cells(CurrentRow, 3).Value
TextBox4.Text = Cells(CurrentRow, 4).Value
TextBox5.Text = Cells(CurrentRow, 5).Value
TextBox6.Text = Cells(CurrentRow, 6).Value
TextBox7.Text = Cells(CurrentRow, 7).Value
TextBox8.Text = Cells(CurrentRow, 8).Value
TextBox9.Text = Cells(CurrentRow, 9).Value
TextBox10.Text = Cells(CurrentRow, 10).Value
TextBox11.Text = Cells(CurrentRow, 11).Value
TextBox12.Text = Cells(CurrentRow, 12).Value
TextBox13.Text = Cells(CurrentRow, 13).Value
TextBox14.Text = Cells(CurrentRow, 14).Value
TextBox15.Text = Cells(CurrentRow, 15).Value
TextBox16.Text = Cells(CurrentRow, 16).Value
TextBox17.Text = Cells(CurrentRow, 17).Value
TextBox18.Text = Cells(CurrentRow, 18).Value
TextBox19.Text = Cells(CurrentRow, 19).Value
TextBox20.Text = Cells(CurrentRow, 20).Value
ElseIf CurrentRow = 1 Then
CurrentRow = CurrentRow + 1
MsgBox "Vous êtes au premier enregistrement"
End If
End Sub
Private Sub CmdQuitter_Click()
Unload Me
End Sub
Private Sub cmdSuivant_Click()
lr = Sheets(1).Range("A100").End(xlUp).Row
CurrentRow = CurrentRow + 1
If CurrentRow = lr + 1 Then
CurrentRow = lr
MsgBox "Vous êtes au dernier enregistrement"
End If
TextBox1.Text = Cells(CurrentRow, 1).Value
TextBox2.Text = Cells(CurrentRow, 2).Value
TextBox3.Text = Cells(CurrentRow, 3).Value
TextBox4.Text = Cells(CurrentRow, 4).Value
TextBox5.Text = Cells(CurrentRow, 5).Value
TextBox6.Text = Cells(CurrentRow, 6).Value
TextBox7.Text = Cells(CurrentRow, 7).Value
TextBox8.Text = Cells(CurrentRow, 8).Value
TextBox9.Text = Cells(CurrentRow, 9).Value
TextBox10.Text = Cells(CurrentRow, 10).Value
TextBox11.Text = Cells(CurrentRow, 11).Value
TextBox12.Text = Cells(CurrentRow, 12).Value
TextBox13.Text = Cells(CurrentRow, 13).Value
TextBox14.Text = Cells(CurrentRow, 14).Value
TextBox15.Text = Cells(CurrentRow, 15).Value
TextBox16.Text = Cells(CurrentRow, 16).Value
TextBox17.Text = Cells(CurrentRow, 17).Value
TextBox18.Text = Cells(CurrentRow, 18).Value
TextBox19.Text = Cells(CurrentRow, 19).Value
TextBox20.Text = Cells(CurrentRow, 20).Value
End Sub
Private Sub CmdValider_Click()
If MsgBox("Validez-vous ces données?", vbYesNo, "Validation") = vbYes Then
lr = Sheets(1).Range("A100").End(xlUp).Row + 1
Cells(lr, 1).Value = Me.TextBox1
Cells(lr, 2).Value = Me.TextBox2
Cells(lr, 3).Value = Me.TextBox3
Cells(lr, 4).Value = Me.TextBox4
Cells(lr, 5).Value = Me.TextBox5
Cells(lr, 6).Value = Me.TextBox6
Cells(lr, 7).Value = Me.TextBox7
Cells(lr, 8).Value = Me.TextBox8
Cells(lr, 9).Value = Me.TextBox9
Cells(lr, 10).Value = Me.TextBox10
Cells(lr, 11).Value = Me.TextBox11
Cells(lr, 12).Value = Me.TextBox12
Cells(lr, 13).Value = Me.TextBox13
Cells(lr, 14).Value = Me.TextBox14
Cells(lr, 15).Value = Me.TextBox15
Cells(lr, 16).Value = Me.TextBox16
Cells(lr, 17).Value = Me.TextBox17
Cells(lr, 18).Value = Me.TextBox18
Cells(lr, 19).Value = Me.TextBox19
Cells(lr, 20).Value = Me.TextBox20
If Me.CheckBox1 = True Then
Cells(lr, 21).Value = "oui"
Else
Cells(lr, 21).Value = "non"
End If
End If
Range("a2").Select
Me.TextBox1 = ""
Me.TextBox2 = ""
Me.TextBox3 = ""
Me.TextBox4 = ""
Me.TextBox5 = ""
Me.TextBox6 = ""
Me.TextBox7 = ""
Me.TextBox8 = ""
Me.TextBox9 = ""
Me.TextBox10 = ""
Me.TextBox11 = ""
Me.TextBox12 = ""
Me.TextBox13 = ""
Me.TextBox14 = ""
Me.TextBox15 = ""
Me.TextBox16 = ""
Me.TextBox17 = ""
Me.TextBox18 = ""
Me.TextBox19 = ""
Me.TextBox20 = ""
End Sub
Private Sub ComboBox1_Change()
Dim Photo As String
i = Me.ComboBox1.ListIndex + 2
Me.TextBox1.Text = Cells(i, 1).Value
Me.TextBox2.Text = Cells(i, 2).Value
Me.TextBox3.Text = Cells(i, 3).Value
Me.TextBox4.Text = Cells(i, 4).Value
Me.TextBox5.Text = Cells(i, 5).Value
Me.TextBox6.Text = Cells(i, 6).Value
Me.TextBox7.Text = Cells(i, 7).Value
Me.TextBox8.Text = Cells(i, 8).Value
Me.TextBox9.Text = Cells(i, 9).Value
Me.TextBox10.Text = Cells(i, 10).Value
Me.TextBox11.Text = Cells(i, 11).Value
Me.TextBox12.Text = Cells(i, 12).Value
Me.TextBox13.Text = Cells(i, 13).Value
Me.TextBox14.Text = Cells(i, 14).Value
Me.TextBox15.Text = Cells(i, 15).Value
Me.TextBox16.Text = Cells(i, 16).Value
Me.TextBox17.Text = Cells(i, 17).Value
Me.TextBox18.Text = Cells(i, 18).Value
Me.TextBox19.Text = Cells(i, 19).Value
Me.TextBox20.Text = Cells(i, 20).Value
On Error GoTo defaut
Photo = ComboBox1.Value
Image1.Picture = LoadPicture("C:\Users\HP\OneDrive\Bureau\Photopersonnels" & Photo & ".jpg")
Exit Sub
defaut:
Image1.Picture = LoadPicture("C:\Users\HP\OneDrive\Bureau\Photopersonnels\Default.jpg")
End Sub
Private Sub TextBox1_Change()
On Error GoTo defaut
Dim Photo As String
Photo = TextBox1.Value
Image1.Picture = LoadPicture("C:\Users\HP\OneDrive\Bureau\Photopersonnels\" & Photo & ".jpg")
Exit Sub
defaut:
Image1.Picture = LoadPicture("C:\Users\HP\OneDrive\Bureau\Photopersonnels\Default.jpg")
End Sub
Private Sub UserForm_Initialize()
CurrentRow = 1
lr = Sheets(1).Range("A100").End(xlUp).Row
For i = 2 To lr
ComboBox1.AddItem Sheets("SOURCE").Cells(i, 1)
Next i
End Sub