Add pictures to comments with code

[Marco-Kun]

I searched for a code to add pictures to comments with VBA. I only found this:

Please Login or Register  to view this content.

With this you can open a picture, which will be entered in the current worksheet.

My Datasheet:
http://www.megaupload.com/?d=FKJ539EG

On the bottom of the list is a button; "Add New Anime". When you click on that you get a UserForm where you can enter; Title, Type, Total Episodes, D/L Eps, Watched Episodes and Status 2. When you press ''Add Anime" the entered values are entered at the bottom of the list, underneath the corresponding list names on the top.

In the list, all anime titles got a picture as comment. Is there a way to combine that with my UserForm and being able to search and add a picture as comment with the newly added title?

[royUK]

attach your workbooks to the post using the manage attachmnts button, zip them if necessary.

[Marco-Kun]

Ok ^^ I had to remove a lot of stuff again, since the original sheet is 8.5mb :P

As you can see in the example added, in cells A156:A160 are pictures when you select the cell. Normally all titles have those pictures. I was wondering if I can add a picture to a comment by adding some kind of option to my Add Anime Userform on the bottom of my list.

[royUK]

This code will check if a cell has a comment, delete an existing one then add a new one and add a selected image

Please Login or Register  to view this content.

[Marco-Kun]

Thank you very much! That one works. I added that to my Add Anime form. Is there a way to make the comment as big as the picture you use?

I would like the cell where the comment is being placed be the same as the Title value cell. In this case A7 when you open the example. I can achieve this of course by selecting the cell before I start the UserForm, but can it be done while having any random cell selected?

Thank you ^^

[royUK]

There is an AutoSize feature for a Comment bu I think it only works with Text.

You need to get the cell to add the comment to when adding the details.

[Marco-Kun]

As for as google takes me, AutoSize only works for text indeed ..

I changed the code a bit to:

Please Login or Register  to view this content.

A190 being an empty cell where the next anime title is going to be when using the form. This way the comment with picture is on the right spot together with the title without having to
select the cell. But yeah .. that only works 1 time.. Is there a way to increase A190 to A191, A192, etc and so on everytime I used the form? Just trying every possible thing :P

[royUK]

I would add the picture when posting the data from the form, give the option to add a picture or not. See example, I've also amended your code in the example to make it a bit faster

[Marco-Kun]

wow thank you That works perfectly.

There is really nothing what can be done about the size of the comment added?

[Marco-Kun]

With all those nifty VBA codes, there really is no way to make the comment as big as the picture you used? :P

[Leith Ross]

Hello Macro-Kun,

Here is a macro I wrote awhile back to insert a Picture from a file as the background for a Comment.

Please Login or Register  to view this content.

[b]Example[/code]

Please Login or Register  to view this content.

[Marco-Kun]

Thank you. I combined it with my Add Anime button on the bottom, and then Add Picture. Now you have to browse for the picture instead of of typing a path name. I noticed that the pictures inserted are somewhat bigger than the actual size. As an example I added the Naruto picture.

And is it possible that, like the info inserted in the form, the picture is placed in the first empty cell below the list?

Thanks
Marco-Kun

[royUK]

The code that I posted yesterday did that

[Marco-Kun]

Oh yes, I am sorry. How can I combine your code and Leith Ross' code? Then it would enter the picture in the comment below the list, and the good size.

[royUK]

I've amended the code for the Add Anime button to incorporate Leith's code to resize the comment

Please Login or Register  to view this content.

[Marco-Kun]

Hello royUK. Thanks for the code. I am not very sure though how to add in on my sheet though. I tried and it seems I messed up somewhere.. since it doesn't work :P

Could you please take a look at it?

Thanks
Marco-Kun

[Leith Ross]

Hello Macro-Kun,

Here is the updated code for your Module5. Since you have Comment indicators turned off, the user must click the cell before the picture becomes visible.

Please Login or Register  to view this content.

[Marco-Kun]

Thank you very much royUK and Leith Ross This works great. I only have one last question:

The code says:

Please Login or Register  to view this content.

While this is WAY better than it was, it isn't the original size. It is bigger. Could this be changed? I added an example list with the right codes, and a picture to see how much bigger it shows.