I am trying to place CRs in a normal Excel text box via vba in an initialization routine. Here is the code I am using:
Option Explicit
Dim iLines As Integer
Dim iCount As Integer
Private Const SCG1 As String = "Line 1"
Private Const SCG2 As String = "Line 2"
Private Const SCG3 As String = "Line 3"
later in code I have the statement:
ActiveSheet.DrawingObjects("Text Box 2").Text = SCG1 & Chr(13) & SCG2 & Chr(13) & SCG3
However, the Chr(13) does not appear as a CR, but instead as a square character. The text does wrap at the margins of the text box. I have also tried using vbnewline instead of chr(13) with the same results. How can I get a real CR into the normal Excel text box
However, when I copy a text box with manually inserted CRs from one workbook to another workbook using:
newWkbk.Worksheets("Cover").DrawingObjects("Text Box 2").Text = oldWkbk.Worksheets("Cover").DrawingObjects("Text Box 2").Text
the CRs are transferred as expected. I must be overlooking something.
Place a CR in normal Text Box via vba
Toldin
Thanks, that did the trick. Interestingly, I had tried >>SCG1 & Chr(13) & Chr(10) & SCG2
and that did not work at all. It gave me the same thing as >>SCG1 & Chr(13) & SCG2>>the square character without a CR
Thanks again.
nubas
If you select the textbox this works:
ActiveSheet.DrawingObjects("Text Box 2").Select
Selection.Characters.Text = SCG1 & Chr(10) & SCG2 & Chr(10) & SCG3
or if you prefer:
Selection.Characters.Text = SCG1 & vbLf & SCG2 & vbLf & SCG3
Edit:
Actually, no need to select, just use the linefeed character in the text string:
ActiveSheet.DrawingObjects("Text Box 2").Text = SCG1 & Chr(10) & SCG2 & Chr(10) & SCG3
Youngd
hi,
use the constant vbCrLf, a new line in Windows is both a carriage return and a line feed.
replace
SCG1 & Chr(13) & SCG2 & Chr(13) & SCG3
with
SCG1 & vbCrLf & SCG2 & vbCrLF & SCG3
Mindy Kelly
Yes, that works, but it still displays the square character at the end of the line. Is there a way to suppress this
Thanks