Create comment box from cell values

[elevdown]

I want to create a comment box in cell A1 on Sheet 1 using a macro.
The comment box will have 3 lines of text.
Line 1 = text from cell A1 on sheet 2.
Line 2 = text from cell F1 on sheet 2.
Line 3 = text from cell E1 on sheet 2.

I want to eventually create 100 comment boxes on Sheet 1 with information from sheet 2.
Not sure how to start this ... thanks for any assistance.

[shg]

You could use a UDF:

Please Login or Register  to view this content.

In any convenient cell on Sheet1,

=SetComment(A1, Sheet2!A1 & CHAR(10) & Sheet2!F1 & CHAR(10) & Sheet2!E1)

[elevdown]

Thanks shg!
This works fine.
If I am going to add similar comments for 99 other cells, is it best to just add 99 formulas in convenient cells on Sheet 1 or is it best to do it all in vba?

Thanks again for your help ... I am going to mark this "solved" as it does exactly what I want!

[shg]

The advantage of formulas is that the references automatically adjust with insertions and deletions (other than deleting a referenced cell).

The advantage of VBA is that it puts everything in one place, and avoids a lot of formulas that will likely confuse people.

[elevdown]

Yes, I see that now ... I believe I will stick with the formulas so that when I edit my sheet2 information the comments will automatically update without having to run a macro to update.

Thanks again!