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

 

[abdelfattah]

this is confused . your code transfer data from tools into one column in listbox and your topic ask transferring data to more than 10 columns .
which is the right?

 

[dss28]

this is confused . your code transfer data from tools into one column in listbox and your topic ask transferring data to more than 10 columns .
which is the right?

i am able to transfer the data to listbox from 9 textboxes. However not from these many

 

[Fluff]

That error would suggest that one (or more) of those controls don't exist.

 

[dss28]

That error would suggest that one (or more) of those controls don't exist.

all the references for the textboxes & combobox are ok.
has it any thing to do with the listbox loading method as the code works fine for 1 to 9 textbox data loading but not beyond that as here it is 16 textboxes+Combobox data

 

[Fluff]

If you are getting the error on the myArr= line then one of those controls doesn't exist.

 

[abdelfattah]

@dss28 you don't answer me .
this try . forgive me if it' doesn't succeed. I'm not at good to write vba

Option Explicit

Dim myArr() As Variant

Private Sub CommandButton1_Click()
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
End Sub

 

[dss28]

@dss28 you don't answer me .
this try . forgive me if it' doesn't succeed. I'm not at good to write vba

Option Explicit

Dim myArr() As Variant

Private Sub CommandButton1_Click()
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
End Sub

yes you are correct the code is placing all the values inn one column in the listbox as per your suggestions
... I was wrong to replace the earlier code with this....

was using for fewer number of textboxes which was working (below code) and found this new on internet as this was not working from start I posted the message

sorry for that

arrCtrls = Array(TextBox1, TextBox2, TextBox3, TextBox4, TextBox5, TextBox6, TextBox19, TextBox20, TextBox21)

With ListBox1
    .AddItem
    For I = LBound(arrCtrls) To UBound(arrCtrls)
        .List(.ListCount - 1, I) = arrCtrls(I).Value
    Next I
End With

 

[Fluff]

Are you getting the type mismatch error on the myArr = line, or are you getting an error somewhere else?

 

[dss28]

Are you getting the type mismatch error on the myArr = line, or are you getting an error somewhere else?

Error on myerr line
But with suggestion suggested above the data was transferred in one column only