Inserting a picture into comment macro

[confusedjack]

Hi there,

I have a spreadsheet with 500 values, each with a seperate image. My objective is to have each of these images inserted as comments to their respective values, as having each one physically take up a cell would make the sheet too large to view.

Inserting these individually would take hours upon hours, so with help I've gotten this far:

Sub Insert_Comment_Picture()
Dim PicNo As Double

For PicNo = 1 To 2
Range("A" & PicNo).AddComment ("")
Range("A" & PicNo).Comment.Shape.Fill.UserPicture "C:\My Pictures\" & PicNo & ".jpg"
Next PicNo

End Sub

The problem with this is that it doesn't actually look at any values in cells, it only inserts the pictures in order.

Basically, I want the macro to look at the values in column E and insert the pics with the same name located in C:\My Pictures\ in column G.

Help please!

 

[repairman615]

I am using 2007. I wonder if that could be the snag.

On my end it works with or without a previous comment. For blank file names in the list it does make blank comments.

I see your signiture, are you still using 2007 to be able to test?

Jeff

 

[repairman615]

Couldn't hurt to fully qualify... I am going to try to throw in a

dim Ws as Worksheet

set Ws= Activesheet

 

[repairman615]

Ant better?

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Insert_Comment_Picture()<br><br><SPAN style="color:#007F00">'''Uses column E:E and insert into column G:G</SPAN><br><br><SPAN style="color:#00007F">Dim</SPAN> PicNo           <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> LR              <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> c               <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> cRng            <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> FR              <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> fPath           <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> Ws              <SPAN style="color:#00007F">As</SPAN> Worksheet<br><br><SPAN style="color:#00007F">Set</SPAN> Ws = ActiveSheet<br><br>fPath = "C:\Users\standard account\Pictures\"   <SPAN style="color:#007F00">'''Change to your file path</SPAN><br><SPAN style="color:#00007F">If</SPAN> Right(fPath, 1) <> "\" <SPAN style="color:#00007F">Then</SPAN> fPath = fPath & "\"<br><br>FR = "E1"   <SPAN style="color:#007F00">'''First cell in column E:E  <------Change to suit</SPAN><br><br>LR = Ws.Range("E" & Rows.Count).End(xlUp).Row<br><SPAN style="color:#00007F">Set</SPAN> cRng = Ws.Range(FR & ":E" & LR)<br><br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> cRng<br>        <SPAN style="color:#00007F">With</SPAN> Ws.Range("G" & c.Row)<br>            .Comment.Delete<br>            .AddComment<br>            .Comment.Shape.Fill.UserPicture fPath & c.Value & ".jpg"<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> c<br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Jaye7]

I will try your new script tomorrow as excel 2010 is on my work computer.

Your previous script also works perfectly in 2003.

I have found several problems with scripts using 2010, unfortunately not too many experts have it at the moment which makes it annoying when my scripts no-longer work, as I am only capable of modifying scripts and cutting bits and pieces from scripts to make new ones.

Thanks Jeff, I will keep you updated.

 

[confusedjack]

Hi there Jaye/Repairman,

Thank you for the prompt responses, Looks like you've done the work and testing for me!

I've tried it out and it seems to work great, you've done me a huge service, thank you.

One last thing, is it at all possible to have the comments resizing to the size of the pictures? At the moment they're all the same size.

Cheers
Jack

 

[Jaye7]

Hi Jeff,

Unfortunately your script from post 13 still isn't working in excel 2010, still getting stuck if there is no comment. Error 91

 

[repairman615]

Here is a last attemp.

confusedjack,
Find the 3 in the code next to scalewidth and scaleheight. this number changes the comment size. bigger number = bigger size.
I am not sure how to set the size depending on the pic.

Jaye7
My thought is to set the object of the with to a variable as seen in the code below.

Surely it has to be possible.
Another option may be to use an IF to test for a comment exist...Then...Else. This would seperate the two cases.

Might even post a new thread, there are several folks here with 2010 and alot more VBA knowledge than me.

Let me know when and how you solve this.



<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Insert_Comment_Picture()<br><br><SPAN style="color:#007F00">'''Uses column E:E and insert into column G:G</SPAN><br><br><SPAN style="color:#00007F">Dim</SPAN> PicNo           <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, LR                  <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, _<br>    c               <SPAN style="color:#00007F">As</SPAN> Range, cRng                  <SPAN style="color:#00007F">As</SPAN> Range, _<br>    FR              <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, fPath                <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, _<br>    Ws              <SPAN style="color:#00007F">As</SPAN> Worksheet, rngComment        <SPAN style="color:#00007F">As</SPAN> Range<br><br><SPAN style="color:#00007F">Set</SPAN> Ws = ActiveSheet<br><br>fPath = "C:\Users\standard account\Pictures\"   <SPAN style="color:#007F00">'''Change to your file path</SPAN><br><SPAN style="color:#00007F">If</SPAN> Right(fPath, 1) <> "\" <SPAN style="color:#00007F">Then</SPAN> fPath = fPath & "\"<br><br>FR = "E1"   <SPAN style="color:#007F00">'''First cell in column E:E  <------Change to suit</SPAN><br><br>LR = Ws.Range("E" & Rows.Count).End(xlUp).Row<br><SPAN style="color:#00007F">Set</SPAN> cRng = Ws.Range(FR & ":E" & LR)<br><br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> cRng<br>        <SPAN style="color:#00007F">Set</SPAN> rngComment = Ws.Range("G" & c.Row)<br>        <SPAN style="color:#00007F">With</SPAN> rngComment<br>            .Comment.Delete<br>            .AddComment<br>            .Comment.Shape.Fill.UserPicture fPath & c.Value & ".jpg"<br>            .Comment.Shape.ScaleWidth 3, msoFalse, msoScaleFromTopLeft<br>            .Comment.Shape.ScaleHeight 3, msoFalse, msoScaleFromTopLeft<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rngComment = <SPAN style="color:#00007F">Nothing</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> c<br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


-Jeff

 

[Jaye7]

Thanks for your help Jeff,

It still isn't work therefore I will do what you suggested and submit it as a new thread.

 

[repairman615]

Hey Jaye7,

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Insert_Comment_Picture()<br><br><SPAN style="color:#007F00">'''Uses column E:E and insert into column G:G</SPAN><br><br><SPAN style="color:#00007F">Dim</SPAN> PicNo           <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, _<br>    LR              <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, _<br>    c               <SPAN style="color:#00007F">As</SPAN> Range, _<br>    cRng            <SPAN style="color:#00007F">As</SPAN> Range, _<br>    FR              <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, _<br>    fPath           <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, _<br>    Ws              <SPAN style="color:#00007F">As</SPAN> Worksheet, _<br>    rngComment      <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br><br><SPAN style="color:#00007F">Set</SPAN> Ws = ActiveSheet<br><br>fPath = "C:\Users\standard account\Pictures\"   <SPAN style="color:#007F00">'''Change to your file path</SPAN><br><SPAN style="color:#00007F">If</SPAN> Right(fPath, 1) <> "\" <SPAN style="color:#00007F">Then</SPAN> fPath = fPath & "\"<br><br>FR = "E1"   <SPAN style="color:#007F00">'''First cell in column E:E  <------Change to suit</SPAN><br><br>LR = Ws.Range("E" & Rows.Count).End(xlUp).Row<br><SPAN style="color:#00007F">Set</SPAN> cRng = Ws.Range(FR & ":E" & LR)<br><br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> cRng<br>        <SPAN style="color:#00007F">Set</SPAN> rngComment = Ws.Range("G" & c.Row)<br>        <SPAN style="color:#00007F">With</SPAN> rngComment<br>            .Comment.Delete<br>            .AddComment<br>            .Comment.Shape.Fill.UserPicture fPath & c.Value & ".jpg"<br>            .Comment.Shape.ScaleWidth 3, msoFalse, msoScaleFromTopLeft<br>            .Comment.Shape.ScaleHeight 3, msoFalse, msoScaleFromTopLeft<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rngComment = <SPAN style="color:#00007F">Nothing</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> c<br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

I changed the rngComment to a Variant type of variable... just reading on the Error 91. I still am guessing though.

 

[Jaye7]

Hi Jeff,

With all the time that you have spent on this I would love to give you some good news, but unfortunately it still does not work.

I thought that maybe an if statement might work, like if the cell has a comment then do this if not do something else, but I'm not sure if that is possible.