Alt Enter vs. Chr(13)

[Guest]

I have entered text into an autoshape and used "Alt-Enter" as CR/LF while
inputting the text. Now reading that text thru vba, I'm trying to replace the
Alt_Enter (shows up as a square in my text string) with a space by using;

txt_1= ActiveSheet.Shapes(SelectedShape).DrawingObject.Caption
txt_2= Replace(txt_1, Chr(13), " ")

This does not work. Any ideas?

 

[Die_Another_Day]

Try Chr(10), this is a line feed. or try Chr(13) & Chr(10), or try
vbCrLf

Charles

 

[witek]

I have entered text into an autoshape and used "Alt-Enter" as CR/LF while
inputting the text. Now reading that text thru vba, I'm trying to replace the
Alt_Enter (shows up as a square in my text string) with a space by using;

txt_1= ActiveSheet.Shapes(SelectedShape).DrawingObject.Caption
txt_2= Replace(txt_1, Chr(13), " ")

This does not work. Any ideas?

try also chr(10) or both.

 

[Andrew Taylor]

Instead of Chr(13) you need to use Chr(10), or
equivalently (and preferably, IMHO) vbLf

Andrew

 

[Guest]

Try:
txt_2= Replace(txt_1, vbCrLf, " ")

If that isn't it, then try replacing chr(13) with chr(10)

 

[NickHK]

Steve,
Use vbLF instead, which is actually ASCII 10.

NickHK
P.S. ASCII 13 is vbCr.

 

[Guest]

Thanks for the quick replies, unfortunately, none of them worked. Forget I
said CR/LF. I guess what I need to know is how can I replace whatever special
code an "Alt Enter" generates with a space?

 

[Die_Another_Day]

txt_2= Replace(txt_1, vbLf, " ")
seems to work for me.

Charles

 

[Andrew Taylor]

Actually I'm confused about this: Alt-Enter is used to create line
breaks in a Cell. but your text is in a Shape, where line breaks
are created by Enter or Ctrl-Enter. I think you need to find exactly
what characters you've got there:

After:
txt_1= ActiveSheet.Shapes(SelectedShape).DrawingObject.Caption
Add this:
Dim i as Integer, c as String
For i = 1 to Len(txt_1)
c = mid(txt_1,i,1)
Debug.Print "Character " & i & " is Chr( " & Asc(c) & ")"
Next

and see what you get..

 

[Guest]

Thank you everyone. The "vbLf" did the trick. I must have typo'd the first
time I tried it. I just used Alt-Enter out of habit when typing in the shapes.

Also,
Thanks Andrew for the debug code.