Display Multiple Cells in TextBox

I have a text box that I need to display multiple cells of data. For instance my data is:

Red | 5
Blue | 6
Green |12
Purple |11

and I need it to display this data in the same general format, but with the code I have now it only displays:

Red | 5

The code I have is
Me.TextBox4.Value = ""

    Dim rngName As Range
    Dim ws As Worksheet
    Dim i As Integer

    Set ws = Worksheets("FUN")
    For i = 1 To ws.Cells(ws.Rows.Count, 1).End(xlUp).Row Step 1
        If ws.Cells(i, 1).Value <> vbNullString Then
            Dim tempVal As String
            tempVal = ws.Cells(i, 1).Value & "                         " & ws.Cells(i, 2).Value
            Me.TextBox4.Text = tempVal
        End If
    Next i

Open in new window

david franciscoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

david franciscoAuthor Commented:
I should clarify, this is a textbox, not a listbox.
0
NorieVBA ExpertCommented:
Try something like this.
Dim rngName As Range
Dim ws As Worksheet
Dim tempVal As String
Dim i As Long

    Set ws = Worksheets("FUN")

    For i = 1 To ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
        If ws.Cells(i, 1).Value <> vbNullString Then
           
            tempVal = tempVal & ws.Cells(i, 1).Value & vbTab & ws.Cells(i, 2).Value & vbCrLf
        End If
    Next i

    
    With Me.TextBox4
        .Value = tempVal
        .MultiLine = True
    End With

Open in new window

0
Ryan ChongCommented:
reason behind is you declare your variable: tempVal in the loop and you didn't append but instead you overwritten TextBox4's value.

another similar version you can refer to (it didn't append the extra carriage return at the last line, if that's important for you):

Sub test()
    Me.TextBox4.Value = ""

    Dim rngName As Range
    Dim ws As Worksheet
    Dim i As Integer
    Dim tempVal As String
    
    Set ws = Worksheets("FUN")
    For i = 1 To ws.Cells(ws.Rows.Count, 1).End(xlUp).Row Step 1
        If ws.Cells(i, 1).Value <> vbNullString Then
            If tempVal <> "" Then
                tempVal = tempVal & vbCrLf
            End If
            tempVal = tempVal & ws.Cells(i, 1).Value & "                         " & ws.Cells(i, 2).Value
            
        End If
    Next i
    
    Me.TextBox4.Text = tempVal
End Sub

Open in new window


you can use the vbTab instead of space that Norie suggested above, all depends on what you want to be generated.
0
NorieVBA ExpertCommented:
Here's another version, using arrays, which allows for there being more than two columns of data.
Dim arrIn As Variant
Dim arrOut()
Dim I As Long
Dim J As Long

    arrIn = Sheets("FUN").Range("A1").CurrentRegion
    
    ReDim arrOut(1 To UBound(arrIn))
    
    For I = 1 To UBound(arrIn)
    
        For J = 1 To UBound(arrIn, 2) - 1
            arrOut(I) = arrOut(I) & arrIn(I, J) & vbTab
        Next J
        
        arrOut(I) = arrOut(I) & arrIn(I, J)
    
    Next I
    
    With TextBox4
        .MultiLine = True
        .Value = Join(arrOut, vbCrLf)
    End With

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.