calling excel genius - cell comment autosize??


Board Regular
Mar 10, 2009
I have the code below, and it works perfectly on my Mac. But... on my PC it does not.

The cell comment does not resize horizontally when I enter text. So if I enter a long comment I get a comment box that extends far to the right to fit the contents in. Vertically it is fine, nothing cut off and no extra space.

Any ideas why this would be? Any ideas how I can force the text to wrap in the cell comment box?

Thanks in advance for your help. Code is below:

Sub AddCom()
Const USERNAME As String = "Greer:"
Dim strCommentName As String
Dim cmnt As String
Dim NoMore As Boolean
Dim Pos As Long
    cmnt = InputBox("Please enter a comment")
    strCommentName = cmnt & vbLf & Now
    On Error GoTo 0

    With activeCell
        If .Comment Is Nothing Then
            strCommentName = USERNAME & Chr(10) & strCommentName
            strCommentName = .Comment.Text & Chr(10) & vbLf & USERNAME & Chr(10) & strCommentName
        End If
        With .AddComment(strCommentName)
            .Visible = False
            .Shape.AutoShapeType = msoShapeRoundedRectangle
            Pos = 0
                Pos = InStr(Pos + 1, strCommentName, USERNAME)
                If Pos > 0 Then
                    With .Shape.TextFrame
                        With .Characters(Pos, Len(USERNAME)).Font
                            .Bold = True
                            .Italic = True
                            .ColorIndex = 3
                        End With
                    End With
                End If
            Loop Until Pos = 0
        End With
        .Comment.Shape.TextFrame.AutoSize = True
        .Comment.Visible = False
    End With
End Sub

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Any Excel genius out there? Really could do with some ideas on how to fix this code.

Upvote 0
I don't claim to be a genuis, but a Google search for "Resize Comment Excel" turned up a lot of hits with some really good code.

Upvote 0
I have searched Google for just those things, unfortunately, I lack the required knowledge to integrate the code I have found into my existing macro. I really need someone to show me how the code above needs to be altered to stop the cell comment box from extending indefinitely to the side. It is very annoying to be so close.

Anyone out there who can help?
Upvote 0
Hi all, I'm hoping someone will have the answer to this, I have searched Google and everything I find seems not to work in the above code so clearly I am missing something. Really need some help.

Upvote 0
This post seems to suggest that if you create one line as the "longest line" with a carriage return it "sets" the limit for autosize....maybe that's useful? Not sure...never got into coding much. If this helps then cheers to bongobongo for sharing his discovery with us all.
Upvote 0
Unfortunately, that didn't work! Any other ideas? Is there a way for me to define when the text is wrapped in the comment, say after 20 characters or whatever?

Also, is there a difference between excel VBA on the Mac vs the PC? because this works on the Mac but not the PC. Presumably if there is a difference, and I knew what it was, that might show me another fix.

Again thanks for your help... but I need more! :)
Upvote 0
I use a Mac.
When I ran the OP code, the lines broke where Excel told them to, not where the Chr(10) indicated.
When I replaced Chr(10) with Chr(13) the lines broke at those points.
This is consistant with Mac's use of Chr(13) as a line break in cells.

Setting the AutoSize property to True or False seems to have no effect.
Upvote 0
so should I change the Chr(10) to Chr(13) to make it work on PC? This is so frustrating seeing it work at home on Mac but not at work on PC where I really need it.

Can I limit the length of the string of text before a new line starts?

Any suggestions would be great, I've trawled Google but nothing I have found works. I can get the width to work at thre expense of autosize so the height stops fitting to the comment.
Upvote 0

Forum statistics

Latest member

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
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 "".
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