Delete specific text from cell comment?

[jmpatrick]

Hello there,

Is it possible to delete specific text from a cell comment with VBA? This is what I use to create a comment:

    sText = ActiveCell.NoteText
    
    If ActiveCell.NoteText <> "" Then
    sText = sText & vbCrLf & "Lot-Specific Floor Plans"
    Else
    sText = sText & "Lot-Specific Floor Plans"
    End If
    
      With ActiveCell
        .ClearComments
        With .AddComment
            .Text sText
            With .Shape
                .TextFrame.Characters(1, InStr(sText, ":")).Font.Bold = False
                .Width = 180
                .Height = 60
                With .TextFrame.Characters.Font
                   .Name = "Tahoma"
                   .Size = 12
                End With
                With .TextFrame
                   .AutoSize = True
                End With
            End With
        End With
    End With

I need a script that will delete the line Lot-Specific Floor Plans from the comment, but keep all other text.

Possible?

 

[Micron]

Maybe
sText = Replace(sText,"Lot-Specific Floor Plans","")
although why add it in the first place only to remove it later?

 

[jmpatrick]

Maybe
sText = Replace(sText,"Lot-Specific Floor Plans","")
although why add it in the first place only to remove it later?

I posted the wrong code. The IF line was me trying things. Your code works fine. The only issue is that it deletes the text correctly, but leaves an unsightly carriage return behind. So it looks like this:

 

[Micron]

The leading carriage return is probably 2 characters long so maybe
sText = Replace(sText,"Lot-Specific Floor Plans","")
If Left(sText,2)=vbCrLf Or Left(sText2)=,2) = vbNewLine Then sText = Left(sText, Len(sText)-2)

That's off the top of my head. I would have to actually test it if it doesn't work for you.
EDIT- hang on, that would be if the CrLf was at the end (on the right)

change to If Left(sText,2)=vbCrLf Or Left(sText,2) = vbNewLine Then sText =Mid(sText,3)
note the correction to a Left function as well

 

[jmpatrick]

Thanks. Will try your code.