Thanks GS!

Finally I've pasted something into a comment box!!

Now another thing... do you know if I'm able to copy formats cos what Ive
pasted is all jumbled etc. Almost need to be able to paste as a table?....

Is this pushing my luck!!

Thanks, Mel



"Gary''s Student" wrote:

How about:

Sub meltad()
Dim v As String
Dim r As Range

Sheets("KPI values").Select
Range("B9:C50").Select
For Each r In Selection
v = v & r.Value
Next
Range("H11").AddComment
Range("H11").Comment.Text Text:=v
End Sub


You can also concatenate CHR(10)'s into v to improve the appearance.
--
Gary's Student


"Meltad" wrote:

Hi,

I'm trying to copy a range in my worksheet then paste this to a single
comment in a neighbouring cell.

So far I've found this code for pasting from the clipboard to a comment
(courtesey of Bob Greenblatt)....

Sub InsertComment()
Dim oComm As DataObject
Set oComm = New DataObject
With ActiveCell
.AddComment
oComm.GetFromClipboard
.Comment.Text Text:=oComm.GetText(1)
End With
End Sub


however I get an error (user type not defined) - so I must have left
something out. Any ideas anyone??

I want to...

Range("H11").AddComment ""
Sheets("KPI values").Select
Range("B9:C50").Select
Selection.Copy

then paste this into the comment box.... ???

THANKS, Mel