Load a pic into ActiveX Image control

[MartinK]

Hello,

I have inserted an Image ActiveX control on my sheet. I can load jpgs in it by choosing Properties - Picture.

I cannot find a way how to load the picture based on a value in a different cell, e.g. A1 = Test1, load Test1.jpg, etc. The Intellisense and AuroList members is betraying me, as usual.

BTW, does anbody have a good link about how the Autolisting help when writting the code helps? Sometimes I write a period (dot) and the members are listed. Sometimes not (like in this case).

Thank you
Martin

 

[bruno.trudo]

You can try this code:

Private Sub Image1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Image1.Picture = LoadPicture(Application.GetOpenFilename)
End Sub

It opens an openfile dialog when you doubleclick the image1.
You can change Application.GetOpenFilename to Range("A1").Value if it contains full path including filename

Pomůže to?

 

[MartinK]

Thanks! Works a treat!
I just tweaked it to:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, [a1]) Is Nothing Then
        Image1.Picture = LoadPicture([a1].Value)
    End If
End Sub

Martin

Dík

 

[Ekim]

Martin,

Here’s another idea.

Your list of JPEG descriptions in A2:A10, without the file extension.

Image ActiveX control – Properties:
BackColor: White
BorderStyle: None
PictureAlignment: Center
PictureSizeMode: fmPictureSizeModeZoom

Macro:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim WS As Worksheet
Dim mPath As String
If Not Application.Intersect(Range("A2:A10"), Target) Is Nothing Then
    mPath = "C:\Documents and Settings\My Pictures\" & Target.Text & ".jpg"
    If Dir(mPath) <> "" Then
        Set WS = ActiveSheet
        WS.OLEObjects("Image1").Object.Picture = LoadPicture(mPath)
    End If
End If
End Sub

Single click an image description in the List.

Regards,

Mike

 

[MartinK]

Brilliant! Thank you!
Why this:

If Dir(mPath) <> "" Then

?
To handle the error if no file in the folder?
Martin

 

[MartinK]

OK, now this is a silly one...
I do not want to save the pic together with the sheet.
How to I "clear" the Image control?
Thanks
Martin

 

[Ivan F Moala]

Image1.Picture = LoadPicture("")

 

[Ekim]

Ivan,

Could not get that to work. Errors out with “Object required”. This works:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim WS As Worksheet
Set WS = Sheets(1)

With WS
   .OLEObjects("Image1").Object.Picture = LoadPicture("")
End With

End Sub

Regards,

Mike

 

[Ivan F Moala]

Hi Mike, it worked for me ??

Are you sure the Image is In Sheets(1) ??

 

[Anigito]

Martin,

Here’s another idea.

Your list of JPEG descriptions in A2:A10, without the file extension.

Image ActiveX control – Properties:
BackColor: White
BorderStyle: None
PictureAlignment: Center
PictureSizeMode: fmPictureSizeModeZoom

Macro:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim WS As Worksheet
Dim mPath As String
If Not Application.Intersect(Range("A2:A10"), Target) Is Nothing Then
    mPath = "C:\Documents and Settings\My Pictures\" & Target.Text & ".jpg"
    If Dir(mPath) <> "" Then
        Set WS = ActiveSheet
        WS.OLEObjects("Image1").Object.Picture = LoadPicture(mPath)
    End If
End If
End Sub

Single click an image description in the List.

Regards,

Mike

Thank you for sharing the knowledge.

I have a question, if I have a list of image URLs which is concatenated from the data of the Excel Table, can I do the same, please?

The alteration to "mPath = "C:\D ... " string is required, I guess. Will you be so kind as to help me with this?