Transfer userform textbox / combobox data to listbox with more than 10 columns

[dss28]

I want to transfer the userform textbox and Combobox data (total more than 15) to listbox.

I am using following code but gives me error " "Run time Error 13- type mismatch" on line :

myArr = Array(TextBox4, TextBox21, TextBox7, TextBox5, TextBox6, TextBox8, TextBox9, TextBox18, TextBox20, TextBox1, TextBox2, ComboBox2, TextBox3, ComboBox1, TextBox12, TextBox14)

please suggest to correct the code

Private Sub Commandbutton1_Click()  'trasnfer data from all textboxes to listbox

Dim myArr() As Long
Dim n As Long


    myArr = Array(TextBox4, TextBox21, TextBox7, TextBox5, TextBox6, TextBox8, TextBox9, TextBox18, TextBox20, TextBox1, TextBox2, ComboBox2, TextBox3, ComboBox1, TextBox12, TextBox14)

    For n = 0 To 15    ' 16 columns required to be added to listbox

    UserForm3.ListBox1.AddItem myArr(n), UserForm3.ListBox1.ListCount
    Next n

'clear form for another line item

UserForm3.TextBox4 = ""
UserForm3.TextBox21 = ""
UserForm3.TextBox7 = ""
UserForm3.TextBox5 = ""
UserForm3.TextBox6 = ""
UserForm3.TextBox8 = ""
UserForm3.TextBox9 = ""
UserForm3.ComboBox1 = ""
UserForm3.TextBox12 = ""
UserForm3.TextBox14 = ""

End Sub

 

[Fluff]

If you are getting an error on this line

myArr = Array(TextBox4, TextBox21, TextBox7, TextBox5, TextBox6, TextBox8, TextBox9, TextBox18, TextBox20, TextBox1, TextBox2, ComboBox2, TextBox3, ComboBox1, TextBox12, TextBox14)

with the code you initially posted, then nothing will get entered into the listbox. So is the code in post#1 the actual code that you are using?

 

[dmt32]

Hi,
I suspect a reason for the error on that line is due to the array variable myArr declared as long data type when it should be a Variant

Dim myArr As Variant

Dave

 

[abdelfattah]

Hi,
I suspect a reason for the error on that line is due to the array variable myArr declared as long data type when it should be a Variant

Dim myArr As Variant

Dave

you're right . I tested two ways

Dim myArr()

or

Dim myArr As Variant

works both

but @dss28 doesn't seem wanting to fill in one column as I understand him. he wants filling to 16 columns for each TOOL. so should wait for him to give us more details

 

[dss28]

If you are getting an error on this line

myArr = Array(TextBox4, TextBox21, TextBox7, TextBox5, TextBox6, TextBox8, TextBox9, TextBox18, TextBox20, TextBox1, TextBox2, ComboBox2, TextBox3, ComboBox1, TextBox12, TextBox14)

with the code you initially posted, then nothing will get entered into the listbox. So is the code in post#1 the actual code that you are using?

I was using the second code initially but observed it can transfer data from only 9 text boxes. So then from Internet I tried first posted code but could not use it as it gave error on myarr line from the start. However by using dim myarr as variant no error but all data came in one column which I wanted to have in 16 columns.

 

[dss28]

you're right . I tested two ways

Dim myArr()

or

Dim myArr As Variant

works both

but @dss28 doesn't seem wanting to fill in one column as I understand him. he wants filling to 16 columns for each TOOL. so should wait for him to give us more details

Yes I want data in 16 columns in list box so my first posted code seems wrong pick. I found out there are other ways of coding where you can transfer data to list box in more than 10 columns.

 

[Fluff]

How about

Private Sub Commandbutton1_Click()  'trasnfer data from all textboxes to listbox

   Dim myArr() As Variant, x As Variant
   Dim n As Long, c As Long
   
   ReDim x(Me.ListBox1.ListCount, 15)
   
   myArr = Array(TextBox4, TextBox21, TextBox7, TextBox5, TextBox6, TextBox8, TextBox9, TextBox18, TextBox20, TextBox1, TextBox2, ComboBox2, TextBox3, ComboBox1, TextBox12, TextBox14)

   For n = 0 To UBound(x) - 1
      For c = 0 To 5
         x(n, c) = Me.ListBox1.List(n, c)
      Next c
   Next n
   For n = 0 To 15    ' 16 columns required to be added to listbox
      x(UBound(x), n) = myArr(n)
   Next n
   Me.ListBox1.List = x
   'clear form for another line item
   
   Me.TextBox4 = ""
   Me.TextBox21 = ""
   Me.TextBox7 = ""
   Me.TextBox5 = ""
   Me.TextBox6 = ""
   Me.TextBox8 = ""
   Me.TextBox9 = ""
   Me.ComboBox1 = ""
   Me.TextBox12 = ""
   Me.TextBox14 = ""

End Sub

 

[dss28]

How about

Private Sub Commandbutton1_Click()  'trasnfer data from all textboxes to listbox

   Dim myArr() As Variant, x As Variant
   Dim n As Long, c As Long
 
   ReDim x(Me.ListBox1.ListCount, 15)
 
   myArr = Array(TextBox4, TextBox21, TextBox7, TextBox5, TextBox6, TextBox8, TextBox9, TextBox18, TextBox20, TextBox1, TextBox2, ComboBox2, TextBox3, ComboBox1, TextBox12, TextBox14)

   For n = 0 To UBound(x) - 1
      For c = 0 To 5
         x(n, c) = Me.ListBox1.List(n, c)
      Next c
   Next n
   For n = 0 To 15    ' 16 columns required to be added to listbox
      x(UBound(x), n) = myArr(n)
   Next n
   Me.ListBox1.List = x
   'clear form for another line item
 
   Me.TextBox4 = ""
   Me.TextBox21 = ""
   Me.TextBox7 = ""
   Me.TextBox5 = ""
   Me.TextBox6 = ""
   Me.TextBox8 = ""
   Me.TextBox9 = ""
   Me.ComboBox1 = ""
   Me.TextBox12 = ""
   Me.TextBox14 = ""

End Sub

thanks ... the code could transfer the data of all textbox and combobox to the list box.
However while adding second line, the data of first line from 7th column onwards in the sequence given above got errased. Again in adding third row, it errased the data of 2nd line from 7th column onwards......
The data of first 6 columns remained each time but txb9,cmb1,txb12,14,18,20,1,2, 3, cmb2 got errased in the previous rows.

so i experimented with the code further and changed the value of "For c = 0 To 5 " to "For c = 0 To 15" which resolved this deletion.

thanks to all of you @abdelfattah, @dmt32, @Fluff for sparing your time for me.

 

[Fluff]

Oops, forgot to change that after testing.
Glad you sorted it & thanks for the feedback.