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 ideas why the function is not defined? What do I need to change?

 

[John_w]

My code works on Windows Excel. I don't have Mac Office so don't know why it doesn't like that line, but try changing:

Function Word_Wrap(text As String, maxLineLength As Integer) As String

to:

Function Word_Wrap(ByVal text As String, ByVal maxLineLength As Integer) As String

If still no good, is there any Help for the Function keyword built in to Mac Excel VBA?

 

[darro]

That didnt work for the Mac, but an earlier code did, and your code did work for the PC so that is great. Thanks to everyone who helped me out.

Much appreciated

 

[xenou]

EDIT:

nevermind - looks like we made it good news.

 

[mikerickson]

Mac VBA does not support Split (or Join or Replace or a dictionary object) which is why the function WordWrap doesn't work on a Mac.
One workaround would be writting a function, mySplit, and having WordWrap call mySplit instead

Function mySplit(InputString As String, Optional Delimiter As String) As Variant
    Dim outRRay() As String
    Dim point As Long, cutPoint As Long
    
    If Delimiter = vbNullString Then Delimiter = " "
    If Application.OperatingSystem Like "*Mac*" Then
        Rem Split emulator for Macs
        InputString = InputString & Delimiter
        ReDim outRRay(0 To ((Len(InputString) / 2) + 1))
        point = 0
        outRRay(0) = vbNullString
        
        Do Until Len(InputString) = 0
            cutPoint = InStr(InputString, Delimiter)
            outRRay(point) = Trim(Left(InputString, cutPoint - 1))
            InputString = Mid(InputString, cutPoint + Len(Delimiter))
            If outRRay(point) <> vbNullString Then point = point + 1
        Loop
        
        If point = 0 Then point = 1
        ReDim Preserve outRRay(0 To point - 1)
        mySplit = outRRay
    Else
        Rem no emulator for Windows
        mySplit = Split(InputString, Delimiter)
    End If
End Function

 

[darro]

Thanks Mike.