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

Maybe look here: social.msdn.microsoft.com/Forums/en-US/…

Tim Williams
– Tim Williams

2021-04-28 06:16:36 +00:00
Commented Apr 28, 2021 at 6:16 — Tim Williams
– Tim Williams, Commented Apr 28, 2021 at 6:16

Brian M Stafford · Accepted Answer · 2021-04-28 23:02:54Z

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

Christofer Weber · Accepted Answer · 2021-04-28 12:51:06Z

For the part where the user presses "enter", it's easy enough. A simple Debug.Print TextBox1.Text should print it as is.
If you want ot do the pseudo code, you could go with

tbText = Split(TextBox1.Text, vbNewLine)
For Each Line In tbText
    Debug.Print Line
Next

Both of these however fail to detect the wordwrap.

I got a slightly hacky approach from this question I used the hidden textbox, to keep the code simpler.

So I created another textbox, named measure, set AutoSize = True, WordWrap = False, Visible = False and set then font options to the same as the first textbox. And used the following code:

Dim i As Long, w As Double, num As Long, memory As String
w = TextBox1.Width
tbText = Split(TextBox1.Text, vbNewLine)
For Each Line In tbText
    measure.Text = Line
    If measure.Width > w Then
shorten:
        memory = measure.Text
        While measure.Width > w
            num = InStrRev(measure.Text, " ")
            measure.Text = Left(measure.Text, num - 1)
            i = Len(memory) - num
        Wend
        Debug.Print measure.Text
        measure.Text = Right(Line, i)
        If measure.Width > w Then
            GoTo shorten
        Else
            Debug.Print measure.Text
        End If
    Else
        Debug.Print Line
    End If
Next

But feel free to use any of the other methods to get the width of the text for this code, or use an approach from the Link that Tim commented.

Collectives™ on Stack Overflow

Excel VBA loop through every row in Textbox multiline

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related