vba code to show pictures in cells needed

[kelly mort]

Hello All,
I wanna find out if there is a way to use vba to pick pictures from a folder and place them in cells on a worksheet. By using IDs from cells to make lookups. That is say I have the dataset as:

Col A Col B
ID PIC
1001

So I will like the vba code use the ID 1001 to lookup the picture in the folder then show it under the PIC. The pictures will be named just as the IDs. If there is a better way of doing this let me know. Thanks
Kelly

 

[kelly mort]

Yes the workbook file is in the same folder with the folder that contains the pictures. And I have it on a flash drive as a copy.

After working on it from my pc then I also create a copy of the folder on the flash drive.

For instance the main folder is named "My Files" with the contents "My Project.xlsm" and "Pictures"

So if the path could locate that main folder, it will be cool for me. Thanks
Kelly

 

[MickG]

I'm not really sure what the problem is, if your picture file is on your pc, it doesn't matter whether you access it from your flash drive (on your pc) or from the Pc itself. its still the same address . It only when you want to use "ThisWorkbook.Path" that the problems arise. Or am I missing something !!!!

 

[kelly mort]

I'm not really sure what the problem is, if your picture file is on your pc, it doesn't matter whether you access it from your flash drive (on your pc) or from the Pc itself. its still the same address . It only when you want to use "ThisWorkbook.Path" that the problems arise. Or am I missing something !!!!

Okay the point is that I am thinking of a situation where I want to open the file on the flash drive from a different pc. There, I will not have the file path available. So I am thinking if there is a way to use the "ThisWorkbook.Path" so that I can always have my pictures with me wherever I go. Whether from the pc or from the flash drive.

 

[MickG]

You will need to store copies of your picture files on your flash drive, then with your workbook on the same file path, call that path in the code with

Set F = FSO.GetFolder(ThisWorkbook.Path)

You may need to add any folder name to the line above if you are storing the pictures in a separate Folder on the Flash drive.

 

[kelly mort]

Yesssss! !! This is what I wanted.

Can I speed up the code a bit? I turned screen updating off, calculation off and events off but it still takes some time to complete. It even freezes for a few seconds.

Then some of the pictures don't get pasted on the sheet. Most of them used to be pasting before .

 

[MickG]

You might try adding a "DoEvents" to the picture loading bit to get all the pictures., Might help.
Loading picture does seem to take a bit of time.
Another option for the whole thing is to place all the pictures (Hidden) in the sheet and then retrieving/making visible/ and placing them as required, based on the Picture names in the sheet.

 

[kelly mort]

You might try adding a "DoEvents" to the picture loading bit to get all the pictures., Might help.
Loading picture does seem to take a bit of time.
Another option for the whole thing is to place all the pictures (Hidden) in the sheet and then retrieving/making visible/ and placing them as required, based on the Picture names in the sheet.

Yes this idea just came to me. I have placed an image control on the sheet . Now I am trying to get it to the image control linked to a textbook on my userform. I will have a total of 20 image controls to do this. So the first image control displays the first item in the listbox and so on.

This is the code I have used to load a control on the form.

Private Sub lst1_Click ()
Dim Picture As String
fPath = ThisWorkbook.Path & "\" & "Pictures"
i = Me.lst1. ListIndex 
On Error Resume Next 
Me.Pic1. Picture = LoadPicture (fPath & "\" & Me.lst1. Column (0, i ) & ".jpg")

If Err = 53 Then
Me.Pic1.Picture = LoadPicture  ("")
End If 
On Error GoTo 0
End Sub

So I will make this a module that I will call when I need it.

Now my issue is how to get to the image controls which no longer reside on the form but on a worksheet. Then here I will not select anything. Just show the images.

 

[MickG]

I'm not sure why you need a "Userform" my idea was a lot simpler.
Have a look at the file below , see if something similar would do you.
https://app.box.com/s/tmtxlbjlqygd6mnj8shp7do6s0lejbzv

 

[kelly mort]

I'm not sure why you need a "Userform" my idea was a lot simpler.
Have a look at the file below , see if something similar would do you.
https://app.box.com/s/tmtxlbjlqygd6mnj8shp7do6s0lejbzv

Okay I have just looked at the file and I think inserting all the pictures one by one is kind of tough. I wish there is a way to the code I have above to get me there. In that case I will have the pictures there already.

Oh okay I used this and I achieved what I wanted . Have a look for me:

Me.Pic1. Picture = LoadPicture (fPath & "\" & Sheet1. Range ("B2" ) & ".jpg")

 

[MickG]

If Pic1 is an image control, then it should work, but that would mean you having an image control for each name in the sheet and tying that image control to the name entered, is that what you want to do ????