Microsoft 365 UserForm - affiche image selon valeur cellules

[Usine à gaz]

Bonjour à toutes et à tous,
Je vous souhaite un beau dimanche

Dans un UserForm récupéré sur le net, j'ai intégré des photos.
Le procès :
Dans la Feuil1, quand clic sur une ligne (cellules A à Zz), la photo qui correspond à la valeur de la cellule "B" de la ligne active s'affiche
Ca fonctionne mais je pense que mes codes peuvent être certainement "améliorés"

Les voici :
Code de la Feuil1

Private Sub Worksheet_SelectionChange(ByVal R As Range)
If Not Intersect(R, Range("b1:zz40")) Is Nothing Then
    Cells(ActiveCell.Row, 1).Select
End If
If Cells(ActiveCell.Row, 2) <> "" Then
    UserForm1.Show 0
End If

If Not Intersect(R, Range("a1:a40")) Is Nothing Then
If Cells(ActiveCell.Row, 2) = 1 Then: UserForm1.Show 0
If Cells(ActiveCell.Row, 2) = 2 Then: UserForm1.Show 0
If Cells(ActiveCell.Row, 2) = 3 Then: UserForm1.Show 0
If Cells(ActiveCell.Row, 2) = 4 Then: UserForm1.Show 0
If Cells(ActiveCell.Row, 2) = 5 Then: UserForm1.Show 0
If Cells(ActiveCell.Row, 2) = 6 Then: UserForm1.Show 0
If Cells(ActiveCell.Row, 2) = 7 Then: UserForm1.Show 0
If Cells(ActiveCell.Row, 2) = 8 Then: UserForm1.Show 0
If Cells(ActiveCell.Row, 2) = 9 Then: UserForm1.Show 0
If Cells(ActiveCell.Row, 2) = 10 Then: UserForm1.Show 0
If Cells(ActiveCell.Row, 2) = 11 Then: UserForm1.Show 0
If Cells(ActiveCell.Row, 2) = 12 Then: UserForm1.Show 0
If Cells(ActiveCell.Row, 2) = 13 Then: UserForm1.Show 0
If Cells(ActiveCell.Row, 2) = 14 Then: UserForm1.Show 0
If Cells(ActiveCell.Row, 2) = 15 Then: UserForm1.Show 0
If Cells(ActiveCell.Row, 2) = 16 Then: UserForm1.Show 0
If Cells(ActiveCell.Row, 2) = 17 Then: UserForm1.Show 0
If Cells(ActiveCell.Row, 2) = 18 Then: UserForm1.Show 0
If Cells(ActiveCell.Row, 2) = 19 Then: UserForm1.Show 0
If Cells(ActiveCell.Row, 2) = 20 Then: UserForm1.Show 0
If Cells(ActiveCell.Row, 2) = 21 Then: UserForm1.Show 0
If Cells(ActiveCell.Row, 2) = 22 Then: UserForm1.Show 0
If Cells(ActiveCell.Row, 2) = 23 Then: UserForm1.Show 0
End If
End Sub

Code UserForm

Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub UserForm_Initialize()
If Cells(ActiveCell.Row, 2) = 1 Then: Me.Controls("Image1").Visible = True
If Cells(ActiveCell.Row, 2) = 2 Then: Me.Controls("Image2").Visible = True
If Cells(ActiveCell.Row, 2) = 3 Then: Me.Controls("Image3").Visible = True
If Cells(ActiveCell.Row, 2) = 4 Then: Me.Controls("Image4").Visible = True
If Cells(ActiveCell.Row, 2) = 5 Then: Me.Controls("Image5").Visible = True
If Cells(ActiveCell.Row, 2) = 6 Then: Me.Controls("Image6").Visible = True
If Cells(ActiveCell.Row, 2) = 7 Then: Me.Controls("Image7").Visible = True
If Cells(ActiveCell.Row, 2) = 8 Then: Me.Controls("Image8").Visible = True
If Cells(ActiveCell.Row, 2) = 9 Then: Me.Controls("Image9").Visible = True
If Cells(ActiveCell.Row, 2) = 10 Then: Me.Controls("Image10").Visible = True
If Cells(ActiveCell.Row, 2) = 11 Then: Me.Controls("Image11").Visible = True
If Cells(ActiveCell.Row, 2) = 12 Then: Me.Controls("Image12").Visible = True
If Cells(ActiveCell.Row, 2) = 13 Then: Me.Controls("Image13").Visible = True
If Cells(ActiveCell.Row, 2) = 14 Then: Me.Controls("Image14").Visible = True
If Cells(ActiveCell.Row, 2) = 15 Then: Me.Controls("Image15").Visible = True
If Cells(ActiveCell.Row, 2) = 16 Then: Me.Controls("Image16").Visible = True
If Cells(ActiveCell.Row, 2) = 17 Then: Me.Controls("Image17").Visible = True
If Cells(ActiveCell.Row, 2) = 18 Then: Me.Controls("Image18").Visible = True
If Cells(ActiveCell.Row, 2) = 19 Then: Me.Controls("Image19").Visible = True
If Cells(ActiveCell.Row, 2) = 20 Then: Me.Controls("Image20").Visible = True
If Cells(ActiveCell.Row, 2) = 21 Then: Me.Controls("Image21").Visible = True
If Cells(ActiveCell.Row, 2) = 22 Then: Me.Controls("Image22").Visible = True
If Cells(ActiveCell.Row, 2) = 23 Then: Me.Controls("Image23").Visible = True
End Sub

Vous auriez mieux ?
Je joins le fichier test.
Avec mes remerciement,
Amicalement,
lionel,

 

[sylvanu]

Bonjour Usine à gaz,
Vous avez bien trouvé votre surnom.
A tester :
Code de la Feuil1

Private Sub Worksheet_SelectionChange(ByVal R As Range)
If Not Intersect(R, Range("b1:zz40")) Is Nothing Then
    Cells(ActiveCell.Row, 1).Select
End If
If Cells(ActiveCell.Row, 2) <> "" Then
    UserForm1.Show 0
End If
If Not Intersect(R, Range("a1:a40")) Is Nothing Then
    If Cells(ActiveCell.Row, 2) >= 1 And Cells(ActiveCell.Row, 2) <= 23 Then UserForm1.Show 0
End If
End Sub

Code UserForm

Private Sub UserForm_Initialize()
If Cells(ActiveCell.Row, 2) >= 1 And Cells(ActiveCell.Row, 2) <= 23 Then
    Me.Controls("Image1").Visible = True
End If
End Sub

NB: Je n'ai pas testé avec le fichier joint car celui ci était un peu trop léger.

 

[Usine à gaz]

Bonjour sylvanu
"Vous avez bien trouvé votre surnom." lol
Merci pour les codes
ça fonctionne ... presque lol
Quelle que soit la ligne cliquée, c'est toujours la même image qui s'affiche.
fichier joint,
lionel,

 

[sylvanu]

Oups !!!!!

Private Sub UserForm_Initialize()
If Cells(ActiveCell.Row, 2) >= 1 And Cells(ActiveCell.Row, 2) <= 23 Then
    Me.Controls("Image" & Cells(ActiveCell.Row, 2)).Visible = True
End If
End Sub

Si contenu =18 alors on affiche Image & 18 soit "Image18"
Ca devrait mieux marcher.

 

[Usine à gaz]

Re,
Super nickel ... Merci
L'usine à Gaz ouillis" te salue
lionel,