Add 7 Rows and Three Columns from Sheet Values to Listbox

[Hmerman]

Hello,
I have a Listbox which in the Properties window I have Set ColumnCount to 3; ColumnHeads to 0; and ColumnWidth to 100 pt; 100 pt; 100 pt.

I want to populate the Listbox with Sheet3 Values from Cells(6, 14)-Cells(11, 14) to Listbox Column 0 and for Listbox Column 1 and 2 from two contiguous columns on Sheet3 to the left i.e. 15 and 16.

My code gets an error '381' on populating of the Listbox: "Could not set Listbox Property. Invalild array index."

My code:

z = 0

        ' Add Headings
        If (lbSundrVerdelUI.ListCount = 0) Then
            lbSundrVerdelUI.AddItem
            lbSundrVerdelUI.List(z, 0) = "Cultivar"
            lbSundrVerdelUI.List(z, 1) = "S no. For Waybill"
            lbSundrVerdelUI.List(z, 2) = "S no. Waybill for Farm" & cbPlFarm.Value
            z = z + 1
        End If

       If cbPlFarm.Value = "FarmX" Then
                  
  lbSundrVerdelUI.AddItem
       lbSundrVerdelUI.List(lbSundrVerdelUI.ListCount - 1, 0) = Sheet3.Cells(6, 14).Value
      lbSundrVerdelUI.List(lbSundrVerdelUI.ListCount - 1, 1) = Sheet3.Cells(6, 15).Value
       lbSundrVerdelUI.List(lbSundrVerdelUI.ListCount - 1, 2) = Sheet3.Cells(6, 16).Value
       lbSundrVerdelUI.List(lbSundrVerdelUI.ListCount - 2, 0) = Sheet3.Cells(7, 14).Value
      lbSundrVerdelUI.List(lbSundrVerdelUI.ListCount - 2, 1) = Sheet3.Cells(7, 15).Value
    lbSundrVerdelUI.List(lbSundrVerdelUI.ListCount - 2, 2) = Sheet3.Cells(7, 16).Value
     [COLOR=#ffff00]l[/COLOR][COLOR=#ff0000]bSundrVerdelUI.List(lbSundrVerdelUI.ListCount - 3, 0) = Sheet3.Cells(8, 14).Value[/COLOR]
    bSundrVerdelUI.List(lbSundrVerdelUI.ListCount - 3, 1) = Sheet3.Cells(8, 15).Value
     lbSundrVerdelUI.List(lbSundrVerdelUI.ListCount - 3, 2) = Sheet3.Cells(8, 16).Value
      lbSundrVerdelUI.List(lbSundrVerdelUI.ListCount - 4, 0) = Sheet3.Cells(9, 14).Value
       lbSundrVerdelUI.List(lbSundrVerdelUI.ListCount - 4, 1) = Sheet3.Cells(9, 15).Value
       lbSundrVerdelUI.List(lbSundrVerdelUI.ListCount - 4, 2) = Sheet3.Cells(9, 16).Value
        lbSundrVerdelUI.List(lbSundrVerdelUI.ListCount - 5, 0) = Sheet3.Cells(10, 14).Value
       lbSundrVerdelUI.List(lbSundrVerdelUI.ListCount - 5, 1) = Sheet3.Cells(10, 15).Value
        lbSundrVerdelUI.List(lbSundrVerdelUI.ListCount - 5, 2) = Sheet3.Cells(10, 16).Value
       lbSundrVerdelUI.List(lbSundrVerdelUI.ListCount - 6, 0) = Sheet3.Cells(11, 14).Value
       lbSundrVerdelUI.List(lbSundrVerdelUI.ListCount - 6, 1) = Sheet3.Cells(11, 15).Value
        lbSundrVerdelUI.List(lbSundrVerdelUI.ListCount - 6, 2) = Sheet3.Cells(11, 16).Value
   End If

The error occurs on the line with red.

Can you please assist?

Regards,

 

[NoSparks]

Could it be a typo on that line (and the next one), appears you're missing the first letter

Using Option Explicit may be a good idea

 

[Hmerman]

Good eye, thanks. The typo is only on the code I typed here though, not in my workbook. Sorry.

Using Option Explicit may be a good idea

I am using Option Explicit

Dim z as Long

was omitted. Sorry once again.

Can you spot anything else that is wrong with my code?

 

[NoSparks]

Reason your code errors at the red line is that the listcount is 2 and you're subtracting 3.
Put this between the lines you're trying to add and see what you actually have at that point.

MsgBox "Current ListCount is " & lbSundrVerdelUI.ListCount

If you're trying to load the list box with the rows in same order as on the sheet,
I'd put the headings as labels above the listbox and use

    If cbPlFarm.Value = "FarmX" Then
        lbSundrVerdelUI.List = Sheet3.Range(Cells(6, 14), Cells(11, 16)).Value
    End If

Here's a link you might be interested in
http://www.snb-vba.eu/VBA_Fill_combobox_listbox_en.html

 

[Hmerman]

Thanks. Will try and give feedback.