Advice for showing image after ListBox entry selected

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,706
Office Version
  1. 2007
Platform
  1. Windows
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.

Rich (BB code):
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

Well-known Member
Joined
Nov 16, 2010
Messages
3,706
Office Version
  1. 2007
Platform
  1. Windows
Sheet name is called DATABASE
Part numbers are in column F path in column G

Headers row 1 part number / path start row 2
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,576
U can trial this untested code...
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
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,114,276
Messages
5,546,915
Members
410,763
Latest member
TSVIVI
Top