How to pop up an image on another worksheet?

[883robert]

Hi all. I've a task in Excel that's proving beyond me. I'm making an inventory of my assets for others to use should I fall under a bus. I have a worksheet listing the assets with their particulars: serial numbers, location, value etc. I want to include photos of each asset. The photos should be included in the Excel file rather than accompanying it in the same folder. That way my family need only have one file to keep track of my assets rather than have to know where the photos are stored so that they can be included.
What I had in mind was a summary worksheet with the details mentioned above and photos carried in other worksheets in the same workbook. By clicking on a cell in the summary worksheet one would see the referenced image. Alternatively one would be taken to the photo worksheet of that asset showing the photo at full size. Then the user could return to the summary worksheet by either clicking its tab or by hitting 'escape'.
Is this doable in Excel? While there are plenty of database programs around that are designed to do this I want something that anyone can use without having to install a new program. For instance my family could send just a single Excel file to the executor of my will to give them all the information they need.
All suggestions gratefully acknowledged.
Thanks. Robert

 

[Micron]

**If you can load images from a sheet into an activex image control, that would be interesting. I know you can do it from a folder, and I don't see why that should be an issue. No one has to worry about where the images are if you include them in the same folder as where the workbook is kept. However, if you're distributing a wb and cannot include a folder, then I guess that's the issue.

**Post 9 here indicates you can do this with an image list control. However, you could run in to problems with certain ActiveX controls when it comes to distributing files that use them.

 

[883robert]

You've put your finger on the problem. If the images need to be in the same folder as the workbook then I'll run into problems when I email the workbook to somebody. Not everybody will be used to receiving a folder by email and having to store that folder in a client's folder. Thanks for the input.
Robert

 

[Jaafar Tribak]

You could store the images in activex controls like activex images, frames etc in the other worksheet and then, simply use code to assign the images to your activex image control via the Picture Property as follows:

Sheet1.Image1.Picture = Sheet2.Image1.Picture

If the stored images are not loaded`stored in activex images (ie:= are excel Picture shapes) then you can copy them to the clipboard via the CopyPcture Method and use a couple of api call to get a StdPicture object from the clipboard picture ( BITMAP or METAFILE ).

Bear in mind that storing the images in the workbook as opposed to storing them in a folder, could substantially increase the size of the workbook depending on the size and quality of the images.

 

[883robert]

Yes, storing images in the workbook does increase its size but storing those images in a folder carries the same size cost. Is there some special characteristic of large workbooks I'm missing?

 

[Micron]

I think the file size limit for Excel is 2GB for 32 bit.

but storing those images in a folder carries the same size cost.

Perhaps for email messages and the like, but not for workbook file size.

Not sure I agree with having a control for every source image when you could have one control that contains an image list, but that's mainly because the subject matter is out of the realm of my experience. Maybe Excel isn't the right tool for what you want but I have to admit I don't know if PowerPoint, Word or any other Office app would be any better.

Maybe rather than email all the info you could use OneDrive or some type of drop box and include instructions to put the image folder as a subfolder of the workbook. Then Excel controls/code can reference them using the path of the workbook. They could name the folder with the workbook whatever they want and the images would be referenced by using the workbook path & the image folder (which they would not rename) and the image controls would just use the path of the workbook and the subfolder reference that contains the images. Not sure if I explained that very well?

 

[Jaafar Tribak]

Have you considered adding comments to the cells and then adding the respective pictures to each cell comment ?

 

[NdNoviceHlp]

Hi *883robert. Maybe something like this would work. Add a sheet and name it "Pictures". List your assets in Sheet1 "A" starting in row 1. Copy and paste pictures of your assets to the "Pictures" sheet and place the picture name in the corresponding row of Sheet1 "B" (ie. The name shown in the upper left name box when you select the picture eg. Picture 1).
Place an active X image control on Sheet1 (Image1). Copy and paste this code...
Workbook code...

Private Sub Workbook_Open()
'assets in Sheet1 "A"(row 1 start); picture numbers in "B"(row 1 start)
'poctures in sheet named "Pictures"
'Image1 on Sheet1
'use chart to create jpg files
With ThisWorkbook.Worksheets("Pictures").ChartObjects.Add(Sheet1.Image1.Left, _
                Sheet1.Image1.Top, Sheet1.Image1.Width, Sheet1.Image1.Height)
End With
With Sheets("Sheet1")
lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
End With
For cnt = 1 To lastrow
Call CreateJPG(CStr(Sheets("Sheet1").Range("A" & cnt).Value), _
                               CStr(Sheets("Sheet1").Range("B" & cnt).Value))
Next cnt
ThisWorkbook.Worksheets("Pictures").ChartObjects _
               (ThisWorkbook.Worksheets("Pictures").ChartObjects.Count).Delete
Sheets("Sheet1").Select
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'delete jpg files
With Sheets("Sheet1")
lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
End With
On Error Resume Next
For cnt = 1 To lastrow
Kill Environ$("temp") & "" & CStr(Sheets("Sheet1").Range("A" & cnt).Value)
Next cnt
End Sub

Sub CreateJPG(FileNm As String, PicName As String)
Dim xRgPic As Shape
ThisWorkbook.Worksheets("Pictures").Activate
Set xRgPic = ThisWorkbook.Worksheets("Pictures").Shapes(PicName)
xRgPic.CopyPicture
With ThisWorkbook.Worksheets("Pictures").ChartObjects _
                      (ThisWorkbook.Worksheets("Pictures").ChartObjects.Count)
.Activate
.Chart.Paste
.Chart.Export Environ$("temp") & "" & FileNm & ".jpg", "JPG"
End With
End Sub

Sheet1 code...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'load jpg file in Image1 when Asset name selected
If Target.Count > 1 Then Exit Sub
With Sheets("Sheet1")
    lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
Set Rng = .Range(.Cells(1, 1), .Cells(lastrow, 1))
End With
On Error Resume Next
If Not Application.Intersect(Target, Rng) Is Nothing Then
Sheets("Sheet1").Image1.Picture = LoadPicture(Environ$("temp") & "" & Target.Text & ".jpg")
End If
On Error GoTo 0
End Sub

Save the workbook and close it. Re-open the workbook. Select the Asset in Sheet1 "A" to display the selected Asset picture in the image control. HTH. Dave

 

[883robert]

This sounds great Dave. I'll give it a run. Sounds very promising. Thanks
Robert

 

[NdNoviceHlp]

Whoops. On review I see this line needs correction...

Kill Environ$("temp") & "" & CStr(Sheets("Sheet1").Range("A" & cnt).Value) & ".jpg"

Missed the file extension. Dave