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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,991
Messages
5,856,683
Members
431,828
Latest member
kARTIK12345

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