calling excel genius - cell comment autosize??

darro

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

Code:
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
        Else
    
            strCommentName = .Comment.Text & Chr(10) & vbLf & USERNAME & Chr(10) & strCommentName
            .Comment.Delete
        End If
     
        With .AddComment(strCommentName)
        
            .Visible = False
            .Shape.AutoShapeType = msoShapeRoundedRectangle
            
            Pos = 0
            Do
            
                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
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

darro

Board Regular
Joined
Mar 10, 2009
Messages
208
Any Excel genius out there? Really could do with some ideas on how to fix this code.

thanks
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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.

lenze
 

darro

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

darro

Board Regular
Joined
Mar 10, 2009
Messages
208

ADVERTISEMENT

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.

Thanks
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,793
Office Version
  1. 2019
Platform
  1. Windows
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.

http://www.vbforums.com/showthread.php?t=412032
 

darro

Board Regular
Joined
Mar 10, 2009
Messages
208

ADVERTISEMENT

Thanks, I will see if removing the Chr(10) will solve the problem. Good spot!
 

darro

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

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,904
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.
 

darro

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

Watch MrExcel Video

Forum statistics

Threads
1,122,464
Messages
5,596,288
Members
414,052
Latest member
Dual Showman

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