Monday, August 18, 2008

VBA opening comments in Excel

'This was a pain in the neck but if there was a better way than trapping the error when no comment, it would be good to know!

Sub test()
Call AppendComment(ActiveSheet.Cells(4, 1), "Let's try again")
End Sub

Public Sub AppendComment(r As Range, sCommentToAdd As String)
Dim sComment As String

On Error GoTo Err_AppendComment

sComment = r.Comment.Text
If sComment = "" Then
r.AddComment (sCommentToAdd)
Else
r.Comment.Delete
r.AddComment sComment & vbNewLine & sCommentToAdd
End If
Exit Sub
Err_AppendComment:
If Err.Number = 91 Then
sComment = ""
Resume Next
Else
MsgBox "Error Appending comment to " & r.Parent.Name & vbNewLine & Err.Description & vbNewLine & "Error Number:" & Err.Number, vbCritical
End If
End Sub

0 Comments:

Post a Comment

Subscribe to Post Comments [Atom]

<< Home