macro for comments

[brinded]

I've seen a few postings on how to set up macros to format comment
windows.

To avoid using the mouse and pull-down menu I've created my own simple
macro for inserting comments - this works fine in essence.
However, the result is a new blank comment in the appropriate cell -
I then have to go to the 'insert' menu and select 'edit comment' to
paste my text, thus defeating the object of avoiding menus.

I've tried to get the macro to paste the clipboard contents into the
comment, but this does not seem to work. Here's my latest failed
attempt:

ActiveCell.AddComment
ActiveCell.comment.Visible = False
ActiveCell.comment.Text Text:="david gleeson:" & vbLf & ""
Selection.ShapeRange.ScaleWidth 6.21, msoFalse, msoScaleFromTopLeft
ActiveCell.comment.Shape.Select True
ActiveCell.comment.Text Text:="blah, blah, blah"
End Sub

Note: In recording the macro I enlarged the size of the comment box (so
that it doesn't look like one long strip when pasting lengthy text), I
selected username and used cmd-B to undo bold type. The latter doesn't
seem to have been logged/recorded in the macro.
Any help appreciated. Regards, David

 

[Bob Greenblatt]

Change the last line from ActiveCell.comment.Text Text:="blah, blah, blah"

To

ActivceCell.Comment.Text Text:=ActiveCell.Comment.Text & "blah, blah, blah"

 

[brinded]

Thanks Bob,
Unfortunately it still doesn't run.
btw - instead of quoting "blah, blah, blah" shouldn't this be an
instruction to use the current contents of the clipboard (as opposed to
literal content from time of recording the macro)?

Microsoft Excel for Mac X service release 1
Mac OS 10.4.7

 

[Bob Greenblatt]

Thanks Bob,
Unfortunately it still doesn't run.
btw - instead of quoting "blah, blah, blah" shouldn't this be an
instruction to use the current contents of the clipboard (as opposed to
literal content from time of recording the macro)?

Microsoft Excel for Mac X service release 1
Mac OS 10.4.7

Change the last line from ActiveCell.comment.Text Text:="blah, blah, blah"

To

ActivceCell.Comment.Text Text:=ActiveCell.Comment.Text & "blah, blah, blah"

Here's a short macro to place the contents of the clipboard into a cell's
comments box:

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