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]

I tried changing chr(10) to chr(13) on the PC but had no effect on the width of the cell comment. It did place a small square character at the start of the comment text though.

Surely there must be a way for this to be made to work. Thanks for your suggestion Mike, any more

 

[mikerickson]

I'm on a Mac, so I can't test if it works on PC.
I do know using vbCR (chr(13)) in place of vbLf (chr(10)) has fixed other line wrapping differences between Mac and PC.

 

[darro]

hmm, ok I will check and see if i missed one

 

[darro]

Nope, I have changed all chr(10) and vbLf to chr(13) and nothing changed but for the small square icon at start.

 

[mikerickson]

Darn. That square is Excels way of saying that it can't print the character as requested.
So, it sounds like vbLf is the one to use.
Perhaps adding this loop (after adjusting the constant to your taste) will work.

Const maxLineLength As Long = 20

cmnt = InputBox("Please enter a comment")

    For i = maxLineLength To Len(cmnt) Step maxLineLength + 1
        cmnt = Left(cmnt, i) & vbLf & Mid(cmnt, i + 1)
    Next

strCommentName = cmnt & vbLf & Now

 

[darro]

thats the closest so far Mike, cheers, it does now limit the width of the box but it has also returned the text a few lines below the user name, any idea how to fix that, thanks for your help, I was beginning to lose hope

 

[darro]

also it sets the Now on the same last line of text, I'd prefer it if it was on the very last line below the text

 

[darro]

fixed the Now problem, but not the extra lines of space, also, this will cut off any text after the limit, any way to stop that happening in the middle of a word? so only if its a space?

 

[darro]

its ok, wasn't your code, I had an extra vbLf in there, so now if it can be arranged so it only limits and then returns the text if the 20th character is a space or nearest space after 20 then this will be perfect and you will officially be a genius

 

[mikerickson]

That loop adds no vbLF at the end or the beginning of the user entered string.