Showing a picture from a list of pictures (2)

[tonkaexcel]

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

[HansV]

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.

[tonkaexcel]

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

[HansV]

I'll get back to you, but it's dinner time here, so it'll be a while...

[HansV]

See the attached zip file. It contains the modified workbook and three images. Extract them to the same folder.

Images.zip

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

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:

Code: Select all

    strPath = strPath & "Images\"

or if the images are in a fixed folder C:\Images, use

Code: Select all

    strPath = "C:\Images\"

(The trailing backslash is mandatory).

Because the workbook now contains VBA code, I saved it as a macro-enabled workbook (.xlsm).

[tonkaexcel]

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

[tonkaexcel]

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

[HansV]

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!

Images.zip

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.

[tonkaexcel]

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

[HansV]

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.

The column with the filenames (column G in the sample workbook) should contain lookuop formulas that return the correct filename belonging to the stock number.

[tonkaexcel]

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

[HammerD]

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?

[HansV]

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.

Images2.zip

[HammerD]

Where/what is the formula? It is not visible in the file.

[HansV]

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.

[HammerD]

Ok - Thanks for the help.
I'll see if I can expand it and make it applicable to my application.