Show cell value(text) in comment box text with a particular arrangement

This is a question based off this post http://www.excelforum.com/showthread.php?t=623335

Specifically I am working with this code from that post

Please Login or Register  to view this content.

The above code
will display the range A1:C4 in the comment box as
A1
B1
C1
A2
B2
C2
A3
B3
C3
A4
B4
C4

I would like to edit the code so that if input the above example range the comment would display the values as
A1 B1 C1
A2 B2 C2
A3 B3 C3
A4 B4 C4



Thanks for your help!

Thanks so much for your quick reply!!!
However can you tweak it so the range of cells can be flexible for example I could display
A1 B1
A2 B2

or
A1 B1 C1 D1 E1
A2 B2 C2 D2 E2
A3 B3 C3 D3 E3

Also how would you add a white space between the values of the cells in the same row?
Thanks!!

Here's the revision you requested, with an extra parameter to specify how many items per row you want.
PS Are you sure you want a function to do this? It looks to me that it should be a SUB.

Please Login or Register  to view this content.

That code causes the cell to show the #VALUE! error and then still displays a result in the comment box but only
A1 B1
A2 B2
A3 B3

even if I input the range A1:C3

Maybe a SUB would be better

oops - I see a missing line that may have caused the error.

Please Login or Register  to view this content.

Hi again

Here's a modified (and tested) SUB version of the previously proposed solution.

Please Login or Register  to view this content.

Note that the comment object will have a default size and the comment, if too large, may not fit. You would have to resize the comment box in that case.

Hope this helps.

Thank you, Thank you!! The sub actually works better because I realized that I wanted to add the comment to cells that already have formulas in them.
Thanks again!

Good to hear.


Note: The 'Application.Volatile' line can be removed. It has no effect in the sub

Now that it's working, please add to my reputation - click on the star at the bottom-left corner of this message.

Best wishes,
- Stu