PDA

View Full Version : Copy cell content and and paste to specific cell as a comment.



gringo287
08-01-2013, 02:32 PM
Hi,

I may be making more of this than needs be and there may be a simpler way of resolving my Quandry..

I'm using :-


Sheets("sheet1").Range("H5:L5").CopySheets("sheet1").Range("H9:L9").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

To take data that amongst other things, contains some text that the user will have typed, ready to be emailed to a manager.
i need the pasted data to all remain in single rowsand not to extend the width of the columns, so i thought the best way to acheive this, would be to just constrain the "text" to a comment box. Each time the above code is triggered, i would need the old comment to be moved down with the rest of the row and a new comment to be created each time in the top row to reflect the new "text" as well as the rest of the data in the other cells.

As i said, this is my thought on the best way to acheive this, but I'm absolutely open to better/easier suggestions.

SamT
08-02-2013, 06:23 AM
Range.AddComment (Comment text or Object.value)

Better:

Range.WrapText = True
Range.ShrinkToFit = True
'Next operation
Range.Paste

gringo287
08-02-2013, 09:05 AM
Thanks SamT

I actually managed to find exactly what i was looking for just before you posted your reponse. Thanks anyway

http://vbadud.blogspot.co.uk/2012/02/how-to-convert-excel-text-to-comments.html