XL 2016 Faire défiler des photos dans un userForm

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
 

Pièces jointes

  • UserForm.jpg
    UserForm.jpg
    282.6 KB · Affichages: 63
  • Base de données.xlsm
    12.7 KB · Affichages: 38

Discussions similaires

Statistiques des forums

Discussions
311 729
Messages
2 081 966
Membres
101 852
dernier inscrit
dthi16088