Add image to cell comment box

[jpenndcl]

I have a set of data with text in one cell and images in a cell adjacent the text to illustrate the text. I am trying to figure out how to add a comment to the cell containing the text and take the image in the adjacent cell and put it in the comment. Is there a way to do this, or do I have to write a macro that saves the images as their own files and then insert the image files into the comment?

 

[phxsportz]

This might help you out...

http://www.xcelfiles.com/VBA_Quick11.html

 

[jpenndcl]

That does help a bit, but I am struggling with modifying the code to select an image that is already in the spreadsheet (I know I can refer to them by name or in order) and fill the comment box with that.

I did find this which might help store a picture in a variable, which I might be able to use with your suggestion to get the desired result ...

http://www.mrexcel.com/archive/VBA/15308.html

I still haven't quite gotten it though

 

[phxsportz]

Just curious, why would you want them stored in the workbook AND inserted into a comment box ?? I think your Workbook size is going to explode out of control..

Remember.. Excel stores pictures as BMP's regardless of the original format and as such.. a simple little 20K JPG can balloon to 500K in nothing flat.. and if it's stored in the Book AND the Comment box, that's now 1mb of Picture..

 

[phxsportz]

Haven't tested it but you might want to try something like
.Fill.UserPicture me.shape.item[1] or something to that effect..

 

[jpenndcl]

Just curious, why would you want them stored in the workbook AND inserted into a comment box ?? I think your Workbook size is going to explode out of control..

Remember.. Excel stores pictures as BMP's regardless of the original format and as such.. a simple little 20K JPG can balloon to 500K in nothing flat.. and if it's stored in the Book AND the Comment box, that's now 1mb of Picture..

the pictures and text were given to me this way and I have not separated them out because it would take a lot of extra time and effort, so unfortunately I am stuck with it being this way

 

[jpenndcl]

Haven't tested it but you might want to try something like
.Fill.UserPicture me.shape.item[1] or something to that effect..

how are you declaring "me" and "item"?

FYI - userpicture wants a string argument, so I am not sure how I can make that work

 

[RoryA]

If you don't have the disk files anywhere, I suspect you may have to copy the pictures to a blank chart, use the Export method of the chart to create the files, then use the Userpicture method with those files.

 

[jpenndcl]

If you don't have the disk files anywhere, I suspect you may have to copy the pictures to a blank chart, use the Export method of the chart to create the files, then use the Userpicture method with those files.

would it be easier to have the pictures in a word file? Can I use an export function from the word file to create the separate image files, which I can then add in to excel through the userpicture function?

 

[RoryA]

What you could do is save the workbook as a web page. That should create a separate directory with all the images in it. You can then use those with the Userpicture property.