cut & paste to comment box

[todd]

why can't I copy a group of items with data in excel and
paste into a comment.

is there anything I can do to get around it blocking me
from doing this.

Thanks Todd

 

[Dave Peterson]

Maybe you could use a userdefined function:

Option Explicit
Function FillComment(ToCell As Range, myInRng As Range) As Variant
Application.Volatile

Dim myCell As Range
Dim myStr As String

myStr = ""
For Each myCell In myInRng.Cells
myStr = myStr & Chr(10) & myCell.Text
Next myCell

myStr = Mid(myStr, 2)

If ToCell.Comment Is Nothing Then
'do nothing
Else
ToCell.Comment.Delete
End If
ToCell.AddComment Text:=myStr

FillComment = myStr

End Function

This'll take the stuff in A7:a14 and put it in a comment for B1.
=fillcomment(B1,A7:A14)
(It also returns the comment to the cell with the formula.)

You could keep the formula or delete it (if A7:a14 changes) or delete it/reuse
it.

(I used chr(10) (alt-enters) to separate the values--you could use spaces or
nothing. But check format|cells|alignment|wraptext in the cell with the formula
so that you can see how it evaluates.)