Showing a picture from a list of pictures (2)
-
- NewLounger
- Posts: 21
- Joined: 11 Feb 2011, 15:11
Showing a picture from a list of pictures (2)
Hi
I've looked at some of the suggested ways of doing this and i'm using Excel 2007 with no success at all. It's no doubt more down to my ability than the info given.
I have a multiple sheet spreadsheet. My main sheet has a lookup column on it with indentifiers on. Another sheet (images) which has a series of images and an identifier column beside them. I would like to display an image beside each identifier on the main sheet, ive never used a macro or VB and i've tried to use the example sheets as they work perfectly, but tailoring them to suit my needs is beyond me.
I do hope someone can help :)
Fingers crossed.
Andy
I've looked at some of the suggested ways of doing this and i'm using Excel 2007 with no success at all. It's no doubt more down to my ability than the info given.
I have a multiple sheet spreadsheet. My main sheet has a lookup column on it with indentifiers on. Another sheet (images) which has a series of images and an identifier column beside them. I would like to display an image beside each identifier on the main sheet, ive never used a macro or VB and i've tried to use the example sheets as they work perfectly, but tailoring them to suit my needs is beyond me.
I do hope someone can help :)
Fingers crossed.
Andy
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Showing a picture from a list of pictures (2)
Welcome to Eileen's Lounge!
Could you attach a (scaled down) sample workbook? Maximum file size is 250 KB, you can zip the workbook if necessary.
Could you attach a (scaled down) sample workbook? Maximum file size is 250 KB, you can zip the workbook if necessary.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 21
- Joined: 11 Feb 2011, 15:11
Re: Showing a picture from a list of pictures (2)
Hi
Thank you so much for responding so quickly. Ideally i'd like to grab the JPG without importing the images but as there are a few hundred.
I've cut the files down with a small number of examples.
Hope you can help.
Thanks again
Andy
Thank you so much for responding so quickly. Ideally i'd like to grab the JPG without importing the images but as there are a few hundred.
I've cut the files down with a small number of examples.
Hope you can help.
Thanks again
Andy
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Showing a picture from a list of pictures (2)
I'll get back to you, but it's dinner time here, so it'll be a while...
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Showing a picture from a list of pictures (2)
See the attached zip file. It contains the modified workbook and three images. Extract them to the same folder.
I created lookup formulas in column G to return the name of the picture file associated with the stock number. This column can be hidden later on.
The following code in the worksheet module loads the picture (if available) whenever the user edits a cell in column B. It is a linked picture, so it doesn't increase the size of the workbook.
This version expects the picture files in the same folder as the workbook, but that can easily be changed. For example if you want to place the pictures in a subfolder Images of the folder containing the workbook, add the following line above the For Each loop:
or if the images are in a fixed folder C:\Images, use
(The trailing backslash is mandatory).
Because the workbook now contains VBA code, I saved it as a macro-enabled workbook (.xlsm).
I created lookup formulas in column G to return the name of the picture file associated with the stock number. This column can be hidden later on.
The following code in the worksheet module loads the picture (if available) whenever the user edits a cell in column B. It is a linked picture, so it doesn't increase the size of the workbook.
Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim strPath As String
Dim strPic As String
If Not Intersect(Range("B:B"), Target) Is Nothing Then
' You can change this as needed
strPath = ActiveWorkbook.Path
If Right(strPath, 1) <> "\" Then
strPath = strPath & "\"
End If
' Loop through the modified cells
For Each rng In Intersect(Range("B:B"), Target)
' Picture file from column G
strPic = rng.Offset(0, 5).Value
With rng.Offset(0, 6)
On Error Resume Next
' Try to delete existing picture
Me.Shapes("Picture" & rng.Row).Delete
' Try to add picture
If strPic <> "" And Dir(strPath & strPic) <> "" Then
Me.Shapes.AddPicture(Filename:=strPath & strPic, _
LinkToFile:=True, SaveWithDocument:=False, _
Left:=.Left, Top:=.Top, Width:=.Width, _
Height:=.Height).Name = "Picture" & rng.Row
End If
On Error GoTo 0
End With
Next rng
End If
End Sub
Code: Select all
strPath = strPath & "Images\"
Code: Select all
strPath = "C:\Images\"
Because the workbook now contains VBA code, I saved it as a macro-enabled workbook (.xlsm).
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 21
- Joined: 11 Feb 2011, 15:11
Re: Showing a picture from a list of pictures (2)
Hi Hans
That's brilliant, thank you. I'll have ago with it and see if i can copy this to the original worksheet. I know it sounds silly but i'm excited :)
I'll come back to you once i've done it (hopefully). Thanks again Hans, really appreciate your prompt reply and help :)
I'll be in touch,
Andy
That's brilliant, thank you. I'll have ago with it and see if i can copy this to the original worksheet. I know it sounds silly but i'm excited :)
I'll come back to you once i've done it (hopefully). Thanks again Hans, really appreciate your prompt reply and help :)
I'll be in touch,
Andy
-
- NewLounger
- Posts: 21
- Joined: 11 Feb 2011, 15:11
Re: Showing a picture from a list of pictures (2)
Hi Hans
Sorry, I must be thick. I have down loaded the zip, and placed on my desktop. Altered the code to read:
strPath = "C:\Documents and Settings\Andy\Desktop\" ...with no joy, then i looked at column "H" on the VT costs sheet and noticed the locations were set to your desktop so altered to mine....still no sign of the images. Once I can see the images i'll try nd figure out how to implement on my original sheet, but I'm struggling to understand. Sorry Hans.
Can i also ask what process i will need to do in column "H" for it to work on my original sheet?
Thanks again for your patience.
Cheers
Andy
Sorry, I must be thick. I have down loaded the zip, and placed on my desktop. Altered the code to read:
strPath = "C:\Documents and Settings\Andy\Desktop\" ...with no joy, then i looked at column "H" on the VT costs sheet and noticed the locations were set to your desktop so altered to mine....still no sign of the images. Once I can see the images i'll try nd figure out how to implement on my original sheet, but I'm struggling to understand. Sorry Hans.
Can i also ask what process i will need to do in column "H" for it to work on my original sheet?
Thanks again for your patience.
Cheers
Andy
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Showing a picture from a list of pictures (2)
I should have removed the pictures from the workbook before saving it.
I have attached a version of the workbook with the pictures removed. If you open it, you won't see any pictures!
If you place the workbook and the pictures in the same location, for example on your desktop, there is no need to modify the code - the code looks for the pictures in the same place where the workbook is stored.
First, make sure that macros are enabled. In Excel 2007/2010, macros are disabled by default, unless you either allow all macros to run, or make the folder containing the workbook a trusted location. You can set this by clicking the Office button, then Excel Options, Trust Center, Trust Center Settings...
Click Trusted Locations to add a folder to your trusted locations. All macros in workbooks in your Trusted Locations are automatically enabled.
Or click Macro Settings to change the overal macro security level. "Enable all macros" is generally not recommended.
You have to edit a cell in column B of the VT5 Kit -Costs sheet to see the picture - the code runs when one or more cells in column B change. So for example, click on cell B2, press F2 to edit it, then press Enter. If macros are enabled, you should see a picture appear in cell H2. Then do the same for cell B3 etc. You can also experiment with copying the value of B2 to B5, for example.
Once the pictures are displayed, they will remain after saving, closing and reopening the workbook. But since the pictures are linked to keep the size of the workbook down, it'll fail if you move the workbook or the pictures.
An alternative would be to store the pictures in the workbook itself, but you mentioned that you wanted to avoid that.
I have attached a version of the workbook with the pictures removed. If you open it, you won't see any pictures!
If you place the workbook and the pictures in the same location, for example on your desktop, there is no need to modify the code - the code looks for the pictures in the same place where the workbook is stored.
First, make sure that macros are enabled. In Excel 2007/2010, macros are disabled by default, unless you either allow all macros to run, or make the folder containing the workbook a trusted location. You can set this by clicking the Office button, then Excel Options, Trust Center, Trust Center Settings...
Click Trusted Locations to add a folder to your trusted locations. All macros in workbooks in your Trusted Locations are automatically enabled.
Or click Macro Settings to change the overal macro security level. "Enable all macros" is generally not recommended.
You have to edit a cell in column B of the VT5 Kit -Costs sheet to see the picture - the code runs when one or more cells in column B change. So for example, click on cell B2, press F2 to edit it, then press Enter. If macros are enabled, you should see a picture appear in cell H2. Then do the same for cell B3 etc. You can also experiment with copying the value of B2 to B5, for example.
Once the pictures are displayed, they will remain after saving, closing and reopening the workbook. But since the pictures are linked to keep the size of the workbook down, it'll fail if you move the workbook or the pictures.
An alternative would be to store the pictures in the workbook itself, but you mentioned that you wanted to avoid that.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 21
- Joined: 11 Feb 2011, 15:11
Re: Showing a picture from a list of pictures (2)
Hi Hans
Thank you so much for your help. I'm trying to transfer the content to my master sheet, but i'm not getting any images up.
I've transferred the iferror lookup to my sheet (although mine does look to another sheet for the lookup info, but i can't see that will make any difference).
I've also copied your VBA code and placed it on the sheets where i want the data to be displayed.
The sheets i have are VT5 cost, a seperate lookup sheet which only displays the least amount of info (like a delivery note) and finaly the images sheet.
The delivery note type sheet is where i'd like the image shown. I guess i'll have to alter the VBA code...I've tried to include my sheet but it's 400kb and i can't remove any more data to attach it for you...sorry i'm a dunce....there's something i'm not doing right, but i can't see what it is.
If you can tell me i'll roll it out on the main sheet.
Again, thank you so much for all your help.
Andy
Thank you so much for your help. I'm trying to transfer the content to my master sheet, but i'm not getting any images up.
I've transferred the iferror lookup to my sheet (although mine does look to another sheet for the lookup info, but i can't see that will make any difference).
I've also copied your VBA code and placed it on the sheets where i want the data to be displayed.
The sheets i have are VT5 cost, a seperate lookup sheet which only displays the least amount of info (like a delivery note) and finaly the images sheet.
The delivery note type sheet is where i'd like the image shown. I guess i'll have to alter the VBA code...I've tried to include my sheet but it's 400kb and i can't remove any more data to attach it for you...sorry i'm a dunce....there's something i'm not doing right, but i can't see what it is.
If you can tell me i'll roll it out on the main sheet.
Again, thank you so much for all your help.
Andy
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Showing a picture from a list of pictures (2)
If you want to display the images on the delivery note sheet, you have to copy the Worksheet_Change procedure into the worksheet module of the delivery note sheet. The code may have to be changed:
- If the stock number is in another column than column B, you have to change both occurrences of Range("B:B"). For example, if the stock number is in column E, use Range("E:E").
- In the line strPic = rng.Offset(0, 5).Value, the number 5 is the offset of the column with the filenames from the column with the stock codes. In the sample workbook, stock codes are in column B and filenames in column G, that is 5 columns to the right.
- In the line With rng.Offset(0, 6), the number 6 is the offset of the column where the pictures should appear from the column with the stock codes. In the sample workbook, the pictures are in column H, that is 6 columns to the right of column B.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 21
- Joined: 11 Feb 2011, 15:11
Re: Showing a picture from a list of pictures (2)
Hi Hans
Thank you so much for all your help. It's brilliant....to be honest i'm still struggling to understand it...but right now, I dont need to...it works! I'll probably fiddle once my excitement has subsided.
Thanks again
Andy
Thank you so much for all your help. It's brilliant....to be honest i'm still struggling to understand it...but right now, I dont need to...it works! I'll probably fiddle once my excitement has subsided.
Thanks again
Andy
-
- NewLounger
- Posts: 5
- Joined: 02 Mar 2011, 21:38
Re: Showing a picture from a list of pictures (2)
Nicely done... I got it to work qith pics outside the workbook... Now, how would I use this with the pictures in the workbook on one of the tabs?
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Showing a picture from a list of pictures (2)
Welcome to Eileen's Lounge!
If you store the images in the workbook, I'd name them after the part number. You can then use this to copy the correct picture.
See the attached version.
If you store the images in the workbook, I'd name them after the part number. You can then use this to copy the correct picture.
See the attached version.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 5
- Joined: 02 Mar 2011, 21:38
Re: Showing a picture from a list of pictures (2)
Where/what is the formula? It is not visible in the file.
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Showing a picture from a list of pictures (2)
I'm not using a formula but VBA code in the worksheet module.
Right-click the sheet tab of the first sheet and select View Code from the popup menu.
You'll see the code for the Worksheet_Change event. This is executed each time the user changes the value of a cell.
Right-click the sheet tab of the first sheet and select View Code from the popup menu.
You'll see the code for the Worksheet_Change event. This is executed each time the user changes the value of a cell.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 5
- Joined: 02 Mar 2011, 21:38
Re: Showing a picture from a list of pictures (2)
Ok - Thanks for the help.
I'll see if I can expand it and make it applicable to my application.
I'll see if I can expand it and make it applicable to my application.