What are the character codes of a carriage return entered in a comment?

[John Wirt]

I need to rewrite a filter that takes regular CR-LF (Chr(13)Chr(10)
characters out of all the comments on a page but leaves carriage returns
entered in typing in the comment.

The latter are apparently NOT the regular vbCRLF pair of characters. I have
written a filter that distinguishes between the two types of carriage
returns by using the vbCRLF constant in a SEARCH worksheet function, but I
also need to detect whether any "soft returns" are included in a comment and
count them. This is necessary in order to know how to size the comment after
filtering it. (The procedure both resizes and changes the shape of the
comment.)

So my question is, what are the characters entered into a comment when one
(manually) types a carriage return into a comment using Insert Comment.

The vbCRLF comments are imbedded in imported text that is used in creating
many of the comments on a page using a VBA procedure.

So the question is, what is the code sequence of the carriage return typed
in when one hits Enter in inserting a comment into a spreadsheet?

Thank you.

John Wirt

[David McRitchie]

Hi John,
Basically Excel only uses CHAR(10) which is LF (line feed)
so in VBA you would replace vbCRLF with vbLF
then count your vbLF

When you hit Alt+Enter you are generating vbLF
or CHR(10)
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"John Wirt" <jwirt@toward.com> wrote in message news:e4h0hR9kFHA.2860@TK2MSFTNGP15.phx.gbl...
> I need to rewrite a filter that takes regular CR-LF (Chr(13)Chr(10)
> characters out of all the comments on a page but leaves carriage returns
> entered in typing in the comment.
>
> The latter are apparently NOT the regular vbCRLF pair of characters. I have
> written a filter that distinguishes between the two types of carriage
> returns by using the vbCRLF constant in a SEARCH worksheet function, but I
> also need to detect whether any "soft returns" are included in a comment and
> count them. This is necessary in order to know how to size the comment after
> filtering it. (The procedure both resizes and changes the shape of the
> comment.)
>
> So my question is, what are the characters entered into a comment when one
> (manually) types a carriage return into a comment using Insert Comment.
>
> The vbCRLF comments are imbedded in imported text that is used in creating
> many of the comments on a page using a VBA procedure.
>
> So the question is, what is the code sequence of the carriage return typed
> in when one hits Enter in inserting a comment into a spreadsheet?
>
> Thank you.
>
> John Wirt
>
>

[davidm]

David,

In VBA, I have long fallen into the habit of using the following interchangeably to move to the next line:

Chr(10)
Chr(13)
VbCrLf
VbCr
VBLf
VbNewLine

Have I taken too much for granted?

[David McRitchie]

At least on a windows machine, within Excel, I'd say yes.

For Char(13) you would see a square bullet as an unprintable
character, and if you don't have cell wrap on in a cell your
would also see it for Char(10). Alt+Enter gets you an automatic
cell wrap without you having to turn it on in cell, format

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"davidm" <davidm.1swxqb_1122609942.7766@excelforum-nospam.com> wrote in message
news:davidm.1swxqb_1122609942.7766@excelforum-nospam.com...
>
> David,
>
> In VBA, I have long fallen into the habit of using the following
> interchangeably to move to the next line:
>
> * Chr(10)*
> * Chr(13)*
> *VbCrLf*
> *VbCr*
> *VBLf*
> *VbNewLine *
>
> Have I taken too much for granted?
>
>
> --
> davidm
> ------------------------------------------------------------------------
> davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
> View this thread: http://www.excelforum.com/showthread...hreadid=391164
>

[Norman Jones]

Hi David,

Try this demo:

Public Sub TestLineFeeds()

Dim Arr As Variant, arr2 As Variant
Dim i As Long

Arr = Array(Chr(10), vbLf, Chr(13), vbCr, vbNewLine, _
vbCr + vbLf, vbCrLf, vbLf + vbCr)

arr2 = Array("Chr(10)", "vbLf", "Chr(13)", "vbCr", "vbNewLine", _
"vbCr + vbLf", "vbCrLf", "vbLf + vbCr")
For i = LBound(Arr) To UBound(Arr)
Cells(i + 1, 1).Value = "Norman" & Arr(i) & "Jones"
Cells(i + 1, 2) = arr2(i)
Next i

End Sub

---
Regards,
Norman



"davidm" <davidm.1swxqb_1122609942.7766@excelforum-nospam.com> wrote in
message news:davidm.1swxqb_1122609942.7766@excelforum-nospam.com...
>
> David,
>
> In VBA, I have long fallen into the habit of using the following
> interchangeably to move to the next line:
>
> * Chr(10)*
> * Chr(13)*
> *VbCrLf*
> *VbCr*
> *VBLf*
> *VbNewLine *
>
> Have I taken too much for granted?
>
>
> --
> davidm
> ------------------------------------------------------------------------
> davidm's Profile:
> http://www.excelforum.com/member.php...o&userid=20645
> View this thread: http://www.excelforum.com/showthread...hreadid=391164
>

[davidm]

Hi norman,

Your test code produced the following worksheet results:

"Norman
Jones" Chr(10)

"Norman
Jones" vbLf

"NormanJones" Chr(13)
"NormanJones" vbCr

"Norman
Jones" vbNewLine

"Norman
Jones" vbCrLf

Norman
Jones" vbCr + vbLf


"Norman
Jones" vbCrLf

The object lesson is that, of the lot, only Chr(10) and vbLf do a clean job as "line separators" of text on the worksheet. However, in the many instances when a function of carriage return is required "in purely VBA environment", as in organizing text on a MsgBox or InputBox, all the versions I proferred pass muster.
Try:

Sub TestCarriageReturn()

Msgbox "This is a" & Chr(10) & "haven"
Msgbox "This is a" & Chr(13) & "haven"
Msgbox "This is a" & vbNewLine & "haven"
Msgbox "This is a" & VbCr & "haven"
Msgbox "This is a" & VbLf & "haven"
Msgbox "This is a" & vbCrLf & "haven"
Msgbox "This is a" & vbCr+Lf & "haven"

End Sub.