Image Popup Coding

[beagrie]

Hello all,

Hopefully someone can help. I have a spreadsheet that I've set up at work to list parts. Each part has three associated files, a CAD Model, a technical drawing, and jpg.

The CAD model and technical drawing both require special software to open and not every computer at work has this software so that is why I included the jpg. Anyone can quickly (even on the dedicated CAD computer the software can take up to 2 minutes to load) see what the part looks like. Currently the layout is something like;

.Part Number.|....Model....|...Drawing...|....Image....|
-------------------------------------------------------
PRMP000001R1 |.click here..|.click here..|.click here..|

The click here's obviously have a hyper link to the files and the models and drawings are in some random locations, but the images are all in one location and are named for their part number (prmp000001r1.jpg, etc).

What I am hoping to do is have it so that when you mouseover the image cell it shows you a pop up of the image, using the part number cell to get the image location so that it doesn't need to be updated for each new part.

Is there a way of doing this without manually setting a comment for each one? There are nearly a thousand parts in the database and manually editing each part wouldn't be worth the time it would take.

Thanks in advance for any help.

 

[Jon von der Heyden]

Hi

I would still consider going down the comment route. Using VBA you could loop thru the range, add a comment and attach a picture to each comment according to the set location and part number.

Debra has various examples of how to code comments - in particular in this one Debra demonstrates how to add an image to a comment.

Using snippets from Debra's examples you should be able to come up with something. Have a crack - and if needs be post your code for further help.

 

[beagrie]

Hey,

Thanks a lot for the link, that did the trick. In fact I only needed to change a couple lines of code and add two lines to make it do what I needed. If it's of interest to anyone, this is the final code;

Sub InsertComment()

Dim rngList As Range
Dim c As Range
Dim cmt As Comment
Dim strPic As String

On Error Resume Next

Set rngList = Range("D2:D14000")
strPic = "C:\Users\ppcad\Documents\PP Parts\Thumbs\"

For Each c In rngList
With c.Offset(0, 0)
Set cmt = c.Comment
If cmt Is Nothing Then
Set cmt = .AddComment

Image = c.Offset(0, -3)

End If
With cmt
.Text Text:=Image
.Shape.Fill.UserPicture strPic & Image & ".jpg"
.Visible = False
.Shape.ScaleWidth 2.6, msoFalse, msoScaleFromTopLeft
.Shape.ScaleHeight 4, msoFalse, msoScaleFromTopLeft
End With
End With
Next c
End Sub