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

dss28

Board Regular
Joined
Sep 3, 2020
Messages
165
Office Version
  1. 2007
Platform
  1. Windows
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



VBA 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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
79,940
Office Version
  1. 365
Platform
  1. Windows
If you are getting an error on this line
VBA Code:
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?
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
7,807
Office Version
  1. 2021
  2. 2019
Platform
  1. Windows
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

Rich (BB code):
Dim myArr As Variant

Dave
 

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
1,289
Office Version
  1. 2019
Platform
  1. Windows
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

Rich (BB code):
Dim myArr As Variant

Dave
you're right . I tested two ways
VBA Code:
Dim myArr()
or

Code:
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

Board Regular
Joined
Sep 3, 2020
Messages
165
Office Version
  1. 2007
Platform
  1. Windows
If you are getting an error on this line
VBA Code:
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

Board Regular
Joined
Sep 3, 2020
Messages
165
Office Version
  1. 2007
Platform
  1. Windows
you're right . I tested two ways
VBA Code:
Dim myArr()
or

Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
79,940
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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
 
Solution

dss28

Board Regular
Joined
Sep 3, 2020
Messages
165
Office Version
  1. 2007
Platform
  1. Windows
How about
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
79,940
Office Version
  1. 365
Platform
  1. Windows
Oops, forgot to change that after testing.
Glad you sorted it & thanks for the feedback.
 

Forum statistics

Threads
1,182,099
Messages
5,933,642
Members
436,902
Latest member
Ameratsu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top