edit cell comments

darro

Board Regular
Joined
Mar 10, 2009
Messages
208
Hi, I have this code, that is working fine, but I have a couple of improvements in mind. What I want to do is have the ability in this code to not only insert a cell comment but to edit the cell comment once it is inserted. Also, I have tested this on a mac and it works fine, but on a pc the comment frame does not autosize. Any ideas why?

Code:
Sub AddNewCom()
     
    Dim strCommentName As String
    Dim cmnt As String
    Dim Today
    Today = Now
    cmnt = InputBox("Please enter a comment")
    strCommentName = "Greer:    " & cmnt & "  :  " & 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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
this has nothing to do with anything, but doesn't the statement GoTo 0 go to line 0 of the procedure?
 
Upvote 0
I guess so, this is a mix of code I have found that almost did what I wanted and bits I have added. The GoTo bit was there and since it works I have left it.

Any ideas on how to edit the cell comment once it is inserted?
 
Upvote 0
I've sorted the autosize issue. But still need to know how to appendmore text to an existing cell comment that my macro has created. Anyone put there know how to do it. Alternatively, is there a macro that opens cellcomments for editing I can run alongside this one?
 
Upvote 0

Forum statistics

Threads
1,217,758
Messages
6,138,439
Members
450,137
Latest member
HANHAN

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top