Advice for showing image after ListBox entry selected

[ipbr21054]

Evening,
Ive nearly completed this but need some help please with regards showing an image after the item has been selected from the ListBox

I have TextBox1 & ListBox1
There is also the ImageBox

I have Table6 which is the stored items that get loaded into the ListBox.
I start to type in the TextBox & the ListBox entries start to narrow down depending on what i type in the TextBox.
Now this is where im stuck.
I would like to select an item from the ListBox & have its photo shown in the ImageBox

Lets say at present the ListBox entries total 22
The photos in question are only say 6

Reason being a different entry = the same Image.
Example of path for image C:\Users\Ian\Desktop\REMOTES ETC\BIKE STUFF\HONDA1.jpg


This is the code i have in use which works.

Option Explicit

Private myList() As Variant
Private Sub ListBox1_Click()
    
       HondaParts.MyPartNumber.Text = ListBox1.Text
          Unload PNForm
       HondaParts.Show
End Sub

Private Sub TextBox1_Change()
       TextBox1 = UCase(TextBox1)
    Static NoMatch As Boolean
    
    If Len(TextBox1) > 0 Or NoMatch Then
        NoMatch = False
        ListBox1.Visible = True
        ListBox1.List = GetCutList()
        If IsEmpty(ListBox1.List(0)) Then
            MsgBox "NO SUCH NUMBER FOUND", vbCritical, "HONDA EPC NUMBER CHECK"
            ListBox1.List = myList
            NoMatch = True
            TextBox1 = vbNullString
            TextBox1.SetFocus
        End If
    Else
        ListBox1.Visible = False
    End If
End Sub
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Select Case Len(TextBox1)
        Case 5, 9
            With TextBox1
                .Text = .Text & "-"
                .SelStart = Len(.Text)
            End With
    End Select
End Sub
Private Sub UserForm_Initialize()
    myList = Range("Table6")
    ListBox1.List = myList
End Sub

Private Function GetCutList() As Variant()
    Dim i As Long
    Dim ret() As Variant
    Dim ret2() As Variant
    Dim x As Long
    
    ReDim ret(UBound(myList, 1), 0)
    For i = 1 To UBound(myList, 1)
        If myList(i, 1) Like "*" & TextBox1 & "*" Then
            ret(x, 0) = myList(i, 1)
            x = x + 1
        End If
    Next
    
    If x > 0 Then
        ReDim ret2(x - 1, 0)
        For i = 0 To x - 1
            ret2(i, 0) = ret(i, 0)
        Next
        GetCutList = ret2
    Else
        GetCutList = Array(Empty, Empty)
    End If
End Function

Private Sub CloseForm_Click()
Unload PNForm
End Sub

 

[ipbr21054]

Sheet name is called DATABASE
Part numbers are in column F path in column G

Headers row 1 part number / path start row 2

 

[NdNoviceHlp]

U can trial this untested code...

'pnform code
Private Sub ListBox1_Click()
Dim Cnt As Integer, Lastrow As Integer
With Sheets("DATABASE")
    Lastrow = .Range("F" & .Rows.Count).End(xlUp).Row
End With
For Cnt = 2 To Lastrow
If Sheets("DATABASE").Range("F" & Cnt).Value = PNForm.ListBox1.Text Then
PNForm.Hide
HondaParts.Show
HondaParts.ImageBox.Picture = LoadPicture(CStr(Sheets("DATABASE").Range("G" & Cnt).Value))
Exit For
End If
Next Cnt
End Sub

'hondaparts userform code
Private Sub CloseForm_Click()
Unload HondaParts
PNForm.Show
End Sub

HTH. Dave

 

[ipbr21054]

Many thanks as that worked spot on.

 

[NdNoviceHlp]

You are welcome. Thanks for posting your outcome. Dave