Removing unwanted 'enter' character from comment

[Blondegirl]

Hi, on my form which adds a record to the active sheet, I have a textbox
which, if data is entered in it, will create a comment on a particular
cell with that data. I have set the textbox to Enterkeybehaviour =
true but when I view the comment later, when the enter key has been
used, as well as generating a new line, it is also creating an unwanted
character shaped like a square at the end of the line. Could someone
advise how to get rid of it. Here is my coding so far:

If TextBox4.Text <> "" Then
With ActiveSheet.Cells(Nextrow, 2).AddComment
Visible = False
Text TextBox4.Text
Shape.TextFrame.AutoSize = True
End With
End If


Thanks.

 

[Ivan Raiminius]

Hi,

does this help?

If TextBox4.Text <> "" Then
With ActiveSheet.Cells(Nextrow, 2).AddComment
..Visible = False
if instr(,textbox4.value,chr(13))<>0 then
..text worsheetfunction.substitute(textbox4.value,chr(13),"")
else
..Text TextBox4.Text
end if
..Shape.TextFrame.AutoSize = True
End With
End If

Regards,
Ivan

 

[Guest]

An easier way:
If TextBox4.Text <> "" Then
With ActiveSheet.Cells(Nextrow, 2).AddComment
..Visible = False
..Text Replace(TextBox4.Text, Chr(13), "")
..Shape.TextFrame.AutoSize = True
End With
End If

 

[Blondegirl]

Brilliant! Thanks a lot for your help, it works a treat.