calling excel genius - cell comment autosize??

[darro]

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

 

[darro]

Any Excel genius out there? Really could do with some ideas on how to fix this code.

thanks

 

[lenze]

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]

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]

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]

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]

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

 

[darro]

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]

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]

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.