Insert line break in wrapped cell via code

Is it possible to insert line break in a wrapped cell through VBA code? (similar to doing Alt-Enter when entering data manually)

I have set the cell's wrap text property to True via VBA code, and I am inserting data into it also through VBA code.

Solution 1:

Yes. The VBA equivalent of AltEnter is to use a linebreak character:

ActiveCell.Value = "I am a " & Chr(10) & "test"

Note that this automatically sets WrapText to True.

Proof:

Sub test()
Dim c As Range
Set c = ActiveCell
c.WrapText = False
MsgBox "Activcell WrapText is " & c.WrapText
c.Value = "I am a " & Chr(10) & "test"
MsgBox "Activcell WrapText is " & c.WrapText
End Sub

Solution 2:

You could also use vbCrLf which corresponds to Chr(13) & Chr(10). As Andy mentions in the comment below, you might be better off using ControlChars.Lf instead though.

Solution 3:

Yes there are two way to add a line feed:

1. Use the existing function from VBA vbCrLf in the string you want to add a line feed, as such:

   Dim text As String

   text = "Hello" & vbCrLf & "World!"

   Worksheets(1).Cells(1, 1) = text

2. Use the Chr() function and pass the ASCII characters 13 and 10 in order to add a line feed, as shown bellow:

   Dim text As String

   text = "Hello" & Chr(13) & Chr(10) & "World!"

   Worksheets(1).Cells(1, 1) = text

In both cases, you will have the same output in cell (1,1) or A1.