Excel VBA loop through every row in Textbox multiline

Question

I have a multiline textbox (with enable to press the Enter key), and I want to loop through every line and get the full line text.

Please note that the textbox word wrap is enabled and if the new line created by wrapping it will be similar to new line (chr(10)), In other words, I need to grab every line of text as it display on the screen and it doesn't matter if its a new line that created by pressing the "Enter" key or just the text wrapping created a new line.

I need somthing like this pseudo code:

for each line in textbox
       Debug.Pring line
next

Answer 1

The GetLines function creates an array where each element of the array is a line from the TextBox passed into the function. I decided to strip out control characters but if this is not desired you can easily change the logic.

Capturing the GetLines return value allows you to loop through the results:

Option Explicit

Private Sub UserForm_Initialize()
   Text1.Text = "This is line 1" & vbNewLine & "This is a long line that will wrap"
End Sub

Private Sub Command1_Click()
   Dim lines() As String
   Dim line As Variant
   
   lines = GetLines(Text1)
   
   For Each line In lines
      Debug.Print line
   Next
End Sub

Private Function GetLines(ByVal tb As MSForms.TextBox) As String()
   Dim i As Integer
   Dim lc As Integer
   Dim c As String
   Dim lines() As String
   
   tb.SetFocus
   
   lc = 0
   ReDim lines(0 To tb.lineCount - 1)

   For i = 0 To Len(tb.Text) - 1
      tb.SelStart = i
      c = Mid(tb.Text, i + 1, 1)
      If Asc(c) >= 32 Then lines(lc) = lines(lc) & c
      If tb.CurLine > lc Then lc = lc + 1
   Next
   
   GetLines = lines
End Function