How do i get image1 control on userform to display a picture on a worksheet?

[DextNo1]

Hi, this is the first time posting threads, not sure what information i need to put into the [Tags:] section below.

I am Using an Excel form, to search for a keyword on any worksheet, get the cell address, Any Text and any Pictures associated with that keyword and Display those Pictures in an image1 control on my Excel form.

I tried the following but got (Run-Time Error 13):

Image1.Picture = ActiveSheet.Shapes.Range(Array("Picture 31"))

Image1.Picture = ActiveSheet.Shapes(2)

I don't want to use the loadpicture function as these pictures were pasted into the excel sheets and there are many Pictures.

 

[jolivanes]

As far as I know, the pictures need to be saved into a file from your worksheet and imported into the Image Control after.

 

[DextNo1]

Is it possible to put the picture into the Excel Clipboard and then set Image1.Picture to the Clipboard somehow?

 

[DextNo1]

As far as I know, the pictures need to be saved into a file from your worksheet and imported into the Image Control after.

Hi Jolivanes,

So that would mean if the User searched for a keyword and had 20 results containing 6 images, i would have to save 120 images to a directory, then i would have to load those images to to my Image1 objects. If the User did another Search which may have 200 images, i would have to save them to the Directory. So i may have to Delete all Images after every Search.

Is it possible to put the picture into the Excel Clipboard and then set Image1.Picture to the Clipboard somehow?

Thanking you for your help with this.

 

[jolivanes]

Re: "searched for a keyword and had 20 results containing 6 images" What does this mean?
How many pictures are in your workbook? The keyword you mention, is that a name of a picture?
It would be simple enough to have a Userform with a ListBox and an Image Control. The Listbox could be populated with all the picture names and when selected, it would be copied into a folder and from the folder imported into the Image control. Following that, it can be deleted from the folder.

 

[DextNo1]

Re: "searched for a keyword and had 20 results containing 6 images" What does this mean?
How many pictures are in your workbook? The keyword you mention, is that a name of a picture?
It would be simple enough to have a Userform with a ListBox and an Image Control. The Listbox could be populated with all the picture names and when selected, it would be copied into a folder and from the folder imported into the Image control. Following that, it can be deleted from the folder.

Hi Jolivanes,

1) The Keyword relates to do Specific Tasks in a software application that I have been put as Notes in an Excel Workbook so i look up how to do that Task. IN my VB Form, when the User (Me) searches how to do something in that software using Keywords, the User will get Text & Screen Shots on how to that Task relating to the Keyword.

2) There may be about a thousand pictures at the moment and growing. The Keyword is not the name of the Picture, it is Excels Generic Name, like: [Picture 78].

Is there a Control that displays Text and Pictures? Maybe the result of the Search can go in that control and hopefully the User can Scroll through the Results.

Thanking you for your help Jolivanes.

 

[jolivanes]

You will need a relation between the picture and the text so if you select the text, it will produce the relevant picture.
We don't have your workbook so we can't comment on any of that but to me the best way would be to name the pictures with a name that makes sense.
"Picture 72.jpg" has no meaning but "Black_Cat.jpg" does.
If you have a list of the keywords you can populate a ListBox or ComboBox with that and when you select one of the keywords in the ListBox/ComboBox it can produce a picture as long as there is a relation between the two.

This is from a relative old file so I am sure it can be made more up-to-date.

In the following example, the pictures are signatures and obviously these are related to a person.
The names of the pictures are the person who's signature it is.
All the names of the people are in Column A on Sheet2

Private Sub UserForm_Initialize()
    Label1.Caption = "Select a " & Sheets("Sheet2").Range("A1").Value    '<---- produces the text "Name". Of no concern here.
    ListBox2.List = Sheets("Sheet2").Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row).Value
End Sub

When selecting a name in the ListBox, it triggers this code. It uses an offset but again, of no concern.
The selected picture will be copied/transferred to the hard drive and named "TempA.jpg"
The picture "TempA.jpg" will then be copied into the Image Control on the Userform.
Picture will then be deleted from the hard drive.

Private Sub ListBox2_Click()
Dim shp As Shape, sh1 As Worksheet, myPic As Shape, cntry As String
Dim tempChartObj As ChartObject
Dim strPath As String
Set sh1 = Worksheets("Sheet2")
strPath = ThisWorkbook.Path & "\TempA.jpg"
    Set myPic = Sheets("Sheet2").Shapes(Sheets("Sheet2").Columns(1).Find(ListBox2).Offset(, 1))
        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
Kill strPath
End Sub

The UserForm has a Label (Label1), a ListBox (ListBox2), an Image Control (Img1) and a command button to exit.

 

[jolivanes]

You have a UserForm ("UserForm1") with a Label ("Label1"), ListBox ("ListBox1"), Image Control ("Image1") and a CommandButton ("CommandButton1")
Label1 is same width as the Listbox and above the ListBox
In a Regular Module:

Sub Give_It_To_Me()
    UserForm1.Show
End Sub

and these for the UserForm1 Module

Private Sub CommandButton1_Click()
    Unload Me
End Sub

Private Sub ListBox1_Change()
Dim strPath As String, myPic As Shape, tempChartObj As ChartObject
Application.ScreenUpdating = False
strPath = ThisWorkbook.Path & "\Temp.jpg"
Set myPic = ActiveSheet.Shapes(ListBox1)
Set tempChartObj = ActiveSheet.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.Image1.Picture = LoadPicture(strPath)
Image1.PictureSizeMode = fmPictureSizeModeStretch
Application.ScreenUpdating = True
End Sub

Private Sub UserForm_Initialize()
Dim shp As Shape, shpArr
Label1.Caption = "Select a picture from the listbox."
    For Each shp In ActiveSheet.Shapes
        If Left(shp.Name, 8) = "Picture " Then shpArr = shpArr & "|" & shp.Name
    Next shp
shpArr = Split(Mid(shpArr, 2), "|")
ListBox1.List = shpArr
End Sub

 

[DextNo1]

You will need a relation between the picture and the text so if you select the text, it will produce the relevant picture.
We don't have your workbook so we can't comment on any of that but to me the best way would be to name the pictures with a name that makes sense.
"Picture 72.jpg" has no meaning but "Black_Cat.jpg" does.
If you have a list of the keywords you can populate a ListBox or ComboBox with that and when you select one of the keywords in the ListBox/ComboBox it can produce a picture as long as there is a relation between the two.

This is from a relative old file so I am sure it can be made more up-to-date.

In the following example, the pictures are signatures and obviously these are related to a person.
The names of the pictures are the person who's signature it is.
All the names of the people are in Column A on Sheet2

Private Sub UserForm_Initialize()
    Label1.Caption = "Select a " & Sheets("Sheet2").Range("A1").Value    '<---- produces the text "Name". Of no concern here.
    ListBox2.List = Sheets("Sheet2").Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row).Value
End Sub

When selecting a name in the ListBox, it triggers this code. It uses an offset but again, of no concern.
The selected picture will be copied/transferred to the hard drive and named "TempA.jpg"
The picture "TempA.jpg" will then be copied into the Image Control on the Userform.
Picture will then be deleted from the hard drive.

Private Sub ListBox2_Click()
Dim shp As Shape, sh1 As Worksheet, myPic As Shape, cntry As String
Dim tempChartObj As ChartObject
Dim strPath As String
Set sh1 = Worksheets("Sheet2")
strPath = ThisWorkbook.Path & "\TempA.jpg"
    Set myPic = Sheets("Sheet2").Shapes(Sheets("Sheet2").Columns(1).Find(ListBox2).Offset(, 1))
        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
Kill strPath
End Sub

The UserForm has a Label (Label1), a ListBox (ListBox2), an Image Control (Img1) and a command button to exit.

Hi Jolivanes,

I will give this a go and get back to you.

 

[DextNo1]

You have a UserForm ("UserForm1") with a Label ("Label1"), ListBox ("ListBox1"), Image Control ("Image1") and a CommandButton ("CommandButton1")
Label1 is same width as the Listbox and above the ListBox
In a Regular Module:

Sub Give_It_To_Me()
    UserForm1.Show
End Sub

and these for the UserForm1 Module

Private Sub CommandButton1_Click()
    Unload Me
End Sub

Private Sub ListBox1_Change()
Dim strPath As String, myPic As Shape, tempChartObj As ChartObject
Application.ScreenUpdating = False
strPath = ThisWorkbook.Path & "\Temp.jpg"
Set myPic = ActiveSheet.Shapes(ListBox1)
Set tempChartObj = ActiveSheet.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.Image1.Picture = LoadPicture(strPath)
Image1.PictureSizeMode = fmPictureSizeModeStretch
Application.ScreenUpdating = True
End Sub

Private Sub UserForm_Initialize()
Dim shp As Shape, shpArr
Label1.Caption = "Select a picture from the listbox."
    For Each shp In ActiveSheet.Shapes
        If Left(shp.Name, 8) = "Picture " Then shpArr = shpArr & "|" & shp.Name
    Next shp
shpArr = Split(Mid(shpArr, 2), "|")
ListBox1.List = shpArr
End Sub

Hi Jolivanes,

I will give this a go and get back to you.