Advice for showing image after ListBox entry selected

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,698
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
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,576
So what's in the listbox... just the name of the image file or does it have the whole path? The image control could be on the same userform as the listbox would be easier but if not, hiding the userform and using a global variable to pass the path string to the next userform would work. Anyways, abit more info is needed. Dave
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,698
Office Version
  1. 2007
Platform
  1. Windows
In the ListBox is the part number.
depending on which part number is selected then depends which image to show.
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,576
Is the part number part of the image file name? Are all the images in 1 folder? What is the folder name? Could U provide an example as we need to determine what the connection is between the listbox selected part number and the image file so that it can be loaded into your image control. Dave
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,698
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

No
Because more than 1 part number is 1 image.
example.
part ABC 123 = image a
Part DEF 456 also = image a

image path.
C:\Users\Ian\Desktop\REMOTES ETC\BIKE STUFF\HONDA1.jpg

Then others are HONDA2.jpg etc etc.
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,576
You're going to have to add the image path connected to the part number(s) somewhere. Maybe in table 6 add another column with the image path associated with each part number and then when a part is listbox selected you can search the table for the corresponding image path. Alternatively, you could hard code the image paths (ie. within the vba code itself) which I really don't recommend. There has to be some nexus between the part number and the file path. Dave
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,698
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Hi,
Well reading what you put last night i came up with this.
It works but as the list becomes longer my code page will also be going down the screen.

I was think of making it shorter / easier.

It would work by having two columns for the database on a worksheet,
One column would be the part numbers & other column Path to image etc.
So the code would be something like look in column 1 for a match from what was selected in the ListBox then load photo from which its path etc is to the right of it in column two.

But i didnt know how to do that part,saves me making the code below longer and longer if it can be written to find/match etc then cell to the right load photo.
This code then should always be the same length

Rich (BB code):
Private Sub ListBox1_Click()
If Me.ListBox1.Value = "35121-MAT-E00" Then
ImageBox.Picture = LoadPicture("C:\Users\Ian\Desktop\REMOTES ETC\BIKE STUFF\HONDA2.jpg")

ElseIf Me.ListBox1.Value = "35121-MBW-601" Then
ImageBox.Picture = LoadPicture("C:\Users\Ian\Desktop\REMOTES ETC\BIKE STUFF\HONDA2.jpg")

ElseIf Me.ListBox1.Value = "35121-MCA-811" Then
ImageBox.Picture = LoadPicture("C:\Users\Ian\Desktop\REMOTES ETC\BIKE STUFF\HONDA4.jpg")

ElseIf Me.ListBox1.Value = "35121-MCJ-D00" Then
ImageBox.Picture = LoadPicture("C:\Users\Ian\Desktop\REMOTES ETC\BIKE STUFF\HONDA2.jpg")

ElseIf Me.ListBox1.Value = "35121-MEH-631" Then
ImageBox.Picture = LoadPicture("C:\Users\Ian\Desktop\REMOTES ETC\BIKE STUFF\HONDA1.jpg")

ElseIf Me.ListBox1.Value = "35121-MFJ-D00" Then
ImageBox.Picture = LoadPicture("C:\Users\Ian\Desktop\REMOTES ETC\BIKE STUFF\HONDA1.jpg")

ElseIf Me.ListBox1.Value = "35121-MFL-641" Then
ImageBox.Picture = LoadPicture("C:\Users\Ian\Desktop\REMOTES ETC\BIKE STUFF\HONDA1.jpg")

ElseIf Me.ListBox1.Value = "35121-MGH-641" Then
ImageBox.Picture = LoadPicture("C:\Users\Ian\Desktop\REMOTES ETC\BIKE STUFF\HONDA1.jpg")

ElseIf Me.ListBox1.Value = "35121-MGP-D63" Then
ImageBox.Picture = LoadPicture("C:\Users\Ian\Desktop\REMOTES ETC\BIKE STUFF\HONDA3.jpg")

ElseIf Me.ListBox1.Value = "35121-MGS-A31" Then
ImageBox.Picture = LoadPicture("C:\Users\Ian\Desktop\REMOTES ETC\BIKE STUFF\1.jpg")

ElseIf Me.ListBox1.Value = "35121-MJE-D02" Then
ImageBox.Picture = LoadPicture("C:\Users\Ian\Desktop\REMOTES ETC\BIKE STUFF\HONDA3.jpg")

ElseIf Me.ListBox1.Value = "35121-MJG-641" Then
ImageBox.Picture = LoadPicture("C:\Users\Ian\Desktop\REMOTES ETC\BIKE STUFF\VALKYRIE.jpg")

ElseIf Me.ListBox1.Value = "72147-MCA-305" Then
ImageBox.Picture = LoadPicture("C:\Users\Ian\Desktop\REMOTES ETC\BIKE STUFF\REMOTE FOB 3 BUTTON.jpg")

ElseIf Me.ListBox1.Value = "72147-MCA-315" Then
ImageBox.Picture = LoadPicture("C:\Users\Ian\Desktop\REMOTES ETC\BIKE STUFF\REMOTE FOB 3 BUTTON.jpg")

ElseIf Me.ListBox1.Value = "5SL-82511-08-00" Then
ImageBox.Picture = LoadPicture("C:\Users\Ian\Desktop\REMOTES ETC\BIKE STUFF\YAMAHA YH35.jpg")

End If
End Sub
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,576
"So the code would be something like look in column 1 for a match from what was selected in the ListBox then load photo from which its path etc is to the right of it in column two.".... This would be best but it seems like if you already have a table couldn't you just add another column and place the path there? The hard code that U posted will work but yuck the upkeep I imagine will be mind numbing. If U go the extra column with path route it's just a matter of maintaining the table, searching the part list for the listbox selected item and then getting the image path using the offset function. Dave
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,698
Office Version
  1. 2007
Platform
  1. Windows
I can keep table 6 no problem,i can add another column also next to it and add the path.
This is where im now stuck in respect of writing the code for what to do.
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,576
Okay what sheet name and column are the part numbers in and what column will the file paths be in. Also does the table start in row 1 or where? Dave
 

Watch MrExcel Video

Forum statistics

Threads
1,114,072
Messages
5,545,822
Members
410,707
Latest member
SanTrapGamer
Top