Listbox entries

[dave8]

I am using an Active-X listbox on my worksheet and trying to add items but having a problem trying to insert the last item. I'm getting a run-time error. Here is what part of the code looks like:

For r = 3 To rowCount
        If Sheets("Sheet2").Range("B" & r).Value = ComboBox1.Value Then
           ListBox2.AddItem Sheets("Sheet2").Range("B" & r).Value
           
           ListBox2.List(i, 0) = Sheets("Sheet2").Range("B" & r).Value
           ListBox2.List(i, 1) = Sheets("Sheet2").Range("C" & r).Value
           ListBox2.List(i, 2) = Sheets("Sheet2").Range("E" & r).Value
           ListBox2.List(i, 3) = Sheets("Sheet2").Range("F" & r).Value
           ListBox2.List(i, 4) = Sheets("Sheet2").Range("G" & r).Value
           ListBox2.List(i, 5) = Sheets("Sheet2").Range("H" & r).Value
           ListBox2.List(i, 6) = Sheets("Sheet2").Range("J" & r).Value
           ListBox2.List(i, 7) = Sheets("Sheet2").Range("K" & r).Value
           ListBox2.List(i, 8) = Sheets("Sheet2").Range("L" & r).Value
           ListBox2.List(i, 9) = ComboBox2.Value
           ListBox2.List(i, 10) = ComboBox4.Value  
          i = i + 1
       End If
     Next

The error occurs at the last ListBox2.List(i, 10) = ... line. Can anyone help me with this error?

 

[Norie]

What is the error?

 

[dave8]

What is the error?

The error I get: Run-Time error "380'. Could not set the list property. Invalid Property Value.

 

[Linde]

What is the value of "ComboBox4.Value" If the value is "" then that would explain the error

 

[Norie]

Dave

I think the problem could be with the no of columns you have in the listbox.

For some reason when using AddItem you can only have 10 columns.

 

[dave8]

Dave

I think the problem could be with the no of columns you have in the listbox.

For some reason when using AddItem you can only have 10 columns.

Do you have any suggestions how to get more than 10 columns in a listbox?

 

[Norie]

Well the obvious one I suppose is to use some other method to do the initial population of the listbox.

One I can think of is to use an array which you can populate by making a few changes to the code you already have.

Or you could cut down on the no of columns.

For example columns 10 and 11 both just display a single value which comes from comboboxes on the form repeated for each row.

Are those 2 columsn really needed?

 

[dave8]

Well the obvious one I suppose is to use some other method to do the initial population of the listbox.

One I can think of is to use an array which you can populate by making a few changes to the code you already have.

Or you could cut down on the no of columns.

For example columns 10 and 11 both just display a single value which comes from comboboxes on the form repeated for each row.

Are those 2 columsn really needed?

Okay, I understand concatenating the values in two columns into one, but I don't understand how to populate using my code into an array.

 

[Norie]

Here's an example based on your code.

    Dim arrValues()

    For R = 2 To Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Row

        If Sheets("Sheet2").Range("B" & R).Value = Combobox1.Value Then

            ReDim Preserve arrValues(ListBox2.ColumnCount, I)
            arrValues(0, I) = Sheets("Sheet2").Range("B" & R).Value
            arrValues(1, I) = Sheets("Sheet2").Range("C" & R).Value
            arrValues(2, I) = Sheets("Sheet2").Range("E" & R).Value
            arrValues(3, I) = Sheets("Sheet2").Range("F" & R).Value
            arrValues(4, I) = Sheets("Sheet2").Range("G" & R).Value
            arrValues(5, I) = Sheets("Sheet2").Range("H" & R).Value
            arrValues(6, I) = Sheets("Sheet2").Range("J" & R).Value
            arrValues(7, I) = Sheets("Sheet2").Range("K" & R).Value
            arrValues(8, I) = Sheets("Sheet2").Range("L" & R).Value
            arrValues(9, I) = ComboBox2.Value
            arrValues(10, I) = ComboBox4.Value
            I = I + 1
        End If
   Next R
        
   ListBox2.List = Application.Transpose(arrValues)

By the way, I was suggesting dropping the 10th and 11th columns completely - you are already displaying the values on the userform.

If the comboboxes might change you could put the values from them into textboxes at the same time you populate the array.

 

[dave8]

Here's an example based on your code.

    Dim arrValues()

    For R = 2 To Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Row

        If Sheets("Sheet2").Range("B" & R).Value = Combobox1.Value Then

            ReDim Preserve arrValues(ListBox2.ColumnCount, I)
            arrValues(0, I) = Sheets("Sheet2").Range("B" & R).Value
            arrValues(1, I) = Sheets("Sheet2").Range("C" & R).Value
            arrValues(2, I) = Sheets("Sheet2").Range("E" & R).Value
            arrValues(3, I) = Sheets("Sheet2").Range("F" & R).Value
            arrValues(4, I) = Sheets("Sheet2").Range("G" & R).Value
            arrValues(5, I) = Sheets("Sheet2").Range("H" & R).Value
            arrValues(6, I) = Sheets("Sheet2").Range("J" & R).Value
            arrValues(7, I) = Sheets("Sheet2").Range("K" & R).Value
            arrValues(8, I) = Sheets("Sheet2").Range("L" & R).Value
            arrValues(9, I) = ComboBox2.Value
            arrValues(10, I) = ComboBox4.Value
            I = I + 1
        End If
   Next R
        
   ListBox2.List = Application.Transpose(arrValues)

By the way, I was suggesting dropping the 10th and 11th columns completely - you are already displaying the values on the userform.

If the comboboxes might change you could put the values from them into textboxes at the same time you populate the array.

Thank you for your help.