insert picture into comment from active worksheet (not from path)

[witamserdecznie]

Hi,

It is quite easy to find on the web how to insert a picture from a harddrive into comment in Excel.

goes like:

If Target.Address = "$K$16" Then
NewPic = "C:\Users\witamserdecznie\Desktop\" & Range("$K$16").Value
Target.Comment.Shape.Fill.UserPicture NewPic
End If

However how to show picture in a comment that is taken from activeworksheet? I.e. The pictures are already hidden in the worksheet and I just want them to show if appropriate value is selected in certain cells. How to do that?

Thanks,
witamserdecznie

 

[Andrew Poulsom]

You need to export the picture to disk and use the resultant file. This may help you:

http://www.contextures.com/xlcomments03.html#Picture

 

[witamserdecznie]

That this is exactly what I did not want to do. Problem then is that I have to send all images with the .xls file.

Is not there an option to show images in a similar way as the following code works:

If Target.Value = "12" Then
ActiveSheet.Shapes("picture 12").Visible = True
Else
ActiveSheet.Shapes("picture 12").Visible = False

The above works quite well (the picture 12 has to be previously imported into excel sheet) but it does not paste image into a comment - which is what I want.

Thanks,
witamserdecznie.

 

[Andrew Poulsom]

You said "how to show picture in a comment that is taken from activeworksheet?". So why are you complaining that you "have to send all images with the .xls file"?

 

[witamserdecznie]

not sure I see your point.

How to take it from active worksheet was and is my main question. By that I mean to take it from a worksheet and not from a harddrive by giving a path, say C:\pics\... in vba.

As a result I sent only one file and do not have to include a folder with pictures as these are imported into worksheet. Hope that is clear now.

Is there a way to do it? or more importantly do you know how to do it?

Thanks,
witamserdecznie

 

[Andrew Poulsom]

The code in the link I gave you exports a picture on a worksheet to disk and uses that (temporary) file, which can be deleted at the end of the procedure with the Kill statement. You do not need to send out any files with your workbook, but it must contain the pictures you want to use.

Here is another example which adds a picture to an Image control, which also requires that the picture is on disk.

http://www.mrexcel.com/forum/showthread.php?t=83429

 

[witamserdecznie]

Thanks. I will test it.