Hi all, I have a macro that inserts a cell comment into the active cell, and I have adjusted the shape and font to my needs, but... what i want to be able to do is then select the cell with added comment and using same macro (ideally) append new text to the comment preserving the original text.
So ctrl K inserts new comment if no comment exists but ctrl K also opens the comment in edit mode if it already exists. Do you see?
The macro as I have it is below, any help would be much appreciated.
I await your excel mastery!
So ctrl K inserts new comment if no comment exists but ctrl K also opens the comment in edit mode if it already exists. Do you see?
The macro as I have it is below, any help would be much appreciated.
I await your excel mastery!
Code:
Sub AddNewCom()
Dim strCommentName As String
Dim cmnt As String
Dim Today
Today = Now
cmnt = InputBox("Please enter a comment")
strCommentName = "User: " & cmnt & vbLf & Now
On Error GoTo 0
If Not activeCell.Comment Is Nothing Then GoTo 0
With activeCell.AddComment(strCommentName)
.Visible = False
.Shape.AutoShapeType = msoShapeRoundedRectangle
With .Shape.TextFrame
With .Characters(0, Len("Greer:")).Font
.Bold = True
.Italic = True
.ColorIndex = 3
End With
End With
End With
With activeCell
.Comment.Shape.TextFrame.AutoSize = True
.Comment.Visible = False
End With
Exit Sub
0:
If Err.Number <> 0 Then MsgBox Err.Description
End Sub