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
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

ajetrumpet

Banned for being rude
Joined
Apr 12, 2008
Messages
569
Office Version
  1. 365
  2. 2016
  3. 2007
Platform
  1. Windows
this has nothing to do with anything, but doesn't the statement GoTo 0 go to line 0 of the procedure?
 

darro

Board Regular
Joined
Mar 10, 2009
Messages
208
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?
 

darro

Board Regular
Joined
Mar 10, 2009
Messages
208
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,841
Messages
5,598,390
Members
414,234
Latest member
grlevesq

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
Top