Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

problem couldn't set the lis property invalid property array index

0

hello

 I  have  many textbox  about 11  .  I try  copying  for  each  textbox  to specific  column into listbox . for  instance   textbox1= ss then column 1  in listbox =ss  and  textbox2=bb  then  column2= bb  and  so  on  for  each  textbox should copy to  next column based on arranged array .

this  is  what  I  have  , but  it  gives  error "couldn't set the lis property invalid property array index" in this  line 

    ListBox1.List(ListBox1.ListCount - 1, L) = Application.Transpose(myArr)
Option Explicit

Dim myArr() As Variant
Private Sub CommandButton1_Click()
Dim L As Long
    myArr = Array(TextBox1, TextBox2, TextBox3, TextBox4, TextBox5, TextBox6, TextBox7, TextBox8, TextBox9, TextBox10, TextBox11)
     For L= 0 To 10 
    ListBox1.List(ListBox1.ListCount - 1, L) = Application.Transpose(myArr)
    Next L
End Sub
Answer
Discuss

Answers

0
Selected Answer

Leopard

You didn't attach a file but trying to add a transposed array (= a "vertical"array) to a list in your loop won't work. Adding each item of the array using AddItem should work (with the new line in bold below):

Option Explicit

Dim myArr() As Variant
Private Sub CommandButton1_Click()
Dim L As Long

myArr = Array(TextBox1, TextBox2, TextBox3, TextBox4, TextBox5, TextBox6, TextBox7, TextBox8, TextBox9, TextBox10, TextBox11)

For L = 0 To 10
    ListBox1.AddItem myArr(L)
Next L

End Sub
From your revised question/file It seems there is a problem since there is a10-column limit (0 to 9) for Lists created using the .AddItem method. For example, this code works For L = 0 To 9 (or less) but will fail if you set the limit to 10 (even though the ColumnCount is set to 11):
Option Explicit

Dim myArr() As Variant
Private Sub CommandButton1_Click()
Dim L As Long
myArr = Array(TextBox1, TextBox2, TextBox3, TextBox4, TextBox5, TextBox6, TextBox7, TextBox8, TextBox9, TextBox10, TextBox11)

ListBox1.ColumnCount = 11
ListBox1.AddItem

For L = 0 To 9
    ListBox1.List(ListBox1.ListCount - 1, L) = myArr(L)
Next L

End Sub
Suggest you look at solution proposed elsewhere: Show data in more than 10 columns in listbox
Discuss

Discussion

hi John 
sorry  about  the  file . I  attached  it 
  yes  it  works , but  not  as  waht  I  want  .  it  fill  one   column in listbox . I  want  filling in 11  columns textbox1=column1,textbox2=column2, textbox3=column3 and  so  on  up  to  finish to  last  column as  in the  code  and  if  I  fill again  should  move  next row  in listbox like   fill in sheet in empty lastrow
leopard (rep: 88) Oct 25, '21 at 11:19 am
Leopard, please see revised Answerr and Link suggestion
John_Ru (rep: 6092) Oct 25, '21 at 2:48 pm
John. yes  the  link is  the  same  thing for  my  request
thanks  so  much
leopard (rep: 88) Oct 25, '21 at 3:31 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login