Add Comments in Excel from Clipboard

[link99sbc]

A few tips on Comments.
[]'Formats all comments on the sheet

Sub FormatAllComments()

For Each x In ActiveSheet.Comments
With x.Shape.TextFrame.Characters.Font
.Name = "System" 'font
.Size = 10
.Bold = True
End With
Next x
End Sub

[]'Formats the comment in the selected cell

Sub CommentFontChange()

With ActiveCell.comment.Shape.TextFrame.Characters.Font
.Name = "System" 'font
.Size = 10
.Bold = True
.ColorIndex = 1
End With
End Sub

[]'Adds text comment to the selected cell
'Sets the font,size,color and autozize the comment box

Sub comment()

Dim objComment As comment

On Error Resume Next
Set objComment = ActiveCell.AddComment
If Err.Number <> 0 Then Set objComment = ActiveCell.comment

With objComment
.Visible = False
.Text Text:="OK"
.Shape.TextFrame.AutoSize = True
End With
With ActiveCell.comment.Shape.TextFrame.Characters.Font
.Name = "System" 'font
.Size = 14
.Bold = True
.ColorIndex = 3
.Shape.TextFrame.AutoSize = True
End With

End Sub

[]'adds comment to selected cell and text from Clipboard

Sub CommentAddOrEdit()

Dim objComment As comment

On Error Resume Next
Set objComment = ActiveCell.AddComment
If Err.Number <> 0 Then Set objComment = ActiveCell.comment

With objComment
.Visible = False
.Text Text:=""
.Shape.TextFrame.AutoSize = True
End With
With ActiveCell.comment.Shape.TextFrame.Characters.Font
.Name = "Arial" 'font
.Size = 10
.Bold = True
.ColorIndex = 1
.Shape.TextFrame.AutoSize = True
End With

Dim cmt As comment
Set cmt = ActiveCell.comment
If cmt Is Nothing Then
Set cmt = ActiveCell.AddComment
cmt.Text Text:=""
End If

'add Clipboard text to selected shape
cmt.Visible = True
cmt.Shape.Select
ActiveSheet.Paste
cmt.Visible = False

End Sub