Print array in textbox

Question

Im doing code where button 1 will get data from worksheet and store in array, and button 2 will print the array in worksheet and textbox.

Public arr As Variant
Private Sub UserForm_Click()
End Sub

Private Sub CommandButton1_Click()
arr = Range("B8:C17")
Range("B8:C17") = Clear
End Sub

Private Sub CommandButton2_Click()
Range("B8:C17") = arr
TextBox1.Text = arr

End Sub    
Private Sub CommandButton3_Click()
Unload Me

End Sub

Everthing is fine excepet it does not print array in textbox. what is wrong here ?

Did you just create a new account to ask this? Your other question is literally right before this one in VBA tag and you haven't even responded, (yes you are asking different things) but I guess it worked since your using it now... — Simon
– Simon, Commented Apr 25, 2021 at 10:55
Yes it works and thanks. I didnt notice that I was log in in different gmail. — REVERSED MUSIC
– REVERSED MUSIC, Commented Apr 25, 2021 at 11:01
You're surely getting an error when trying to populate the textbox? So you're trying to populate it in the same arrangement as the array correct? So multiple lines in the textbox and from what you have there 2 columns? Does it need to be textbox because a listbox has the built in columns control whereas textboxes do not have multi columns, which you could only replicate by using tab or something. — Simon
– Simon, Commented Apr 25, 2021 at 11:10
It needs to be a textbox. Maybe I'll use tabs to show it neatly. but i cant print it in textbox — REVERSED MUSIC
– REVERSED MUSIC, Commented Apr 25, 2021 at 11:16

Simon · Accepted Answer · 2021-04-25 11:37:54Z

2

So what you need to do is loop through your rows of array data and populate it one by one.

Firstly, you need to make sure to set Multiline to True in your textbox properties.

Then add this:

Private Sub CommandButton2_Click()

Range("B8:C17") = arr

Dim i As Long
For i = 1 To UBound(arr, 1)
    Me.TextBox1.Text = Me.TextBox1.Text & arr(i, 1) & Chr(9) & arr(i, 2) & vbCr
Next i

End Sub

Note:

This will only work for 2 columns in the array. If you have more, you will need to add another & arr(i, x) to the line with x being the column number.

If you need an extra tab between the 2 columns, add another & Chr(9) to the line next to the already existing one. See if that works for you.

edited Apr 25, 2021 at 11:37

answered Apr 25, 2021 at 11:22

Simon

1,3752 gold badges11 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Simon Over a year ago

Now I'm not sure your intentions with this but if you're wanting to edit it, then add the stuff back into another array, that will be a lot more complex.

REVERSED MUSIC Over a year ago

This line :For i = 1 To UBound(arr) shows error that "Type mismatch" I m sured i follow everything fine

Simon Over a year ago

Try For i = 1 To UBound(arr,1)

REVERSED MUSIC Over a year ago

Works fine now. Thank you very much

Simon Over a year ago

No worries, you got the error because it's a 2d array and ubound needed to know whether to do it to the row count or the column count. Different systems have different needs. Mine works fine without adding it in (does rows by default) so that's why I didn't see the problem at first.

Collectives™ on Stack Overflow

Print array in textbox

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related