Pictures from file in userform.

[Tom.Jones]

Hi,

I get a file with one sheet.
In column A (starting in A2) is only one word, in column B (starting in B2) is a pictures and in column C (starting in C2) are descriptions.
How can I do in a userform, to choose one of the word in column A, list the picture and descriptions of that picture.
Can someone help me, with a VBA code, to do all things explained here.
In a Userform, combobox choose the word, and display that picture and in a textbox (or label) show description.
Thank you.

 

[ranman256]

load image control onto form then like:

Private Sub UserForm_Activate()

vFile = "c:\temp\cat.jpg"      ' or:  vFile =cboBox

Image1.Picture = LoadPicture(vFile)
txtbox1 = vFile
End Sub

 

[Tom.Jones]

Thanks for replay,
Pictures are already in sheet1, column B.
How can I load them in userform?

 

[Tom.Jones]

It's impossible to this?

 

[jolivanes]

"In column A (starting in A2) is only one word"
Is that the name of the picture to the right of it? If so, does it have an extension like ".jpg"?

 

[Tom.Jones]

Thank you for replay.

In column A (starting in A2) is only one word - in every cell is only one word.
"Is that the name of the picture to the right of it?" Yes
"If so, does it have an extension like ".jpg"? No

Thanks.

 

[jolivanes]

A Userform with
ListBox named "ListBox1" (witout double quotes)
Label named "Label1" (witout double quotes) and placed above the ListBox
Image control named "Img1" (witout double quotes)
TextBox named "TextBox1" (witout double quotes)

Following both snippets of code go in the UserForm1 code window

Private Sub UserForm_Initialize()
Label1.Caption = Sheets("Sheet1").Range("A1").Value
ListBox1.List = Sheets("Sheet1").Range("A2:A" & Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row).Value
End Sub

Private Sub ListBox1_Change()
Dim shp As Shape, sh1 As Worksheet, myPic As Shape, info As String
Dim tempChartObj As ChartObject
Dim strPath As String
Set sh1 = Worksheets("Sheet1")
strPath = ThisWorkbook.Path & "\Temp.jpg"

    For Each shp In sh1.Shapes
        If shp.TopLeftCell.Address = sh1.Columns(1).Find(ListBox1, , , 1).Offset(, 1).Address Then
            Set myPic = shp
                info = myPic.TopLeftCell.Offset(, 1).Value
            Exit For
        End If
    Next shp
   
    Set tempChartObj = sh1.ChartObjects.Add(100, 100, myPic.Width, myPic.Height)
        myPic.Copy
        DoEvents
            tempChartObj.Chart.ChartArea.Select
        DoEvents
    tempChartObj.Chart.Paste
        tempChartObj.Chart.Export strPath
        tempChartObj.Delete
    Me.Img1.Picture = LoadPicture(strPath)
   
Img1.PictureSizeMode = fmPictureSizeModeStretch
Me.TextBox1 = info
End Sub

 

[Tom.Jones]

Excellent. Thank you.

Please help me with 2 small modifications:
Label1.Caption = Sheets("Foaie3").Range("A1").Value
Need to show Sheets("Foaie3").Range("A1").Value & with what is clicked in ListBox1

Thank you

 

[jolivanes]

By rights, you should have the result in another textbox because the purpose of the textbox1 is not for that.
Change references like sheet names as required.

Me.TextBox1 = info
    Label1.Caption = Sheets("Sheet1").Range("A1").Value & " " & ListBox1    '<---- Add this line
End Sub

 

[Tom.Jones]

Excellent !!!
Thank you so much.