Multicolumn listbox - fill one column from a sheet column

[davedave24]

xpost from ozgrid

'lo. I have a multicolumn listbox - the first 2 columns are filled from A and B from the worksheet. When the user selects a customer name (from a combobox), I need to load that particular customer's price list into column 3 of the listbox. The prices are stored on one sheet (ListProducts), in columns C onwards.

Here is my code and where it dies:

    Dim rngFind As Range
    
    [COLOR=#4E9A06] 'Find the cell containing the customer name in row 1[/color]
    With ThisWorkbook.Sheets("ProductList").Range("A1:EE1")
        Set rngFind = .Find(comboCustomer, , xlValues)
    End With

    Dim cell As Range
    Dim Rng As Range
    
    [COLOR=#4E9A06]'load that column until we hit a blank cell[/color]
    With ThisWorkbook.Sheets("ProductList")
        Set Rng = .Range(rngFind.Address, .Range(rngFind.Address).End(xlDown))
    End With
   
    [COLOR=#4E9A06]'add the column to column2 of the listbox[/color]
    For Each cell In Rng.Cells
        With Me.listProducts
            [COLOR=#CC0000].AddItem cell.Value     ''ERROR - Permission Denied''[/color]
            .List(.ListCount - 1, 2) = cell.Offset(0, 1).Value

        End With
    Next cell

As you can see it gets an error on the .additem line. When I mouseover 'cell.Value', it returns the customer name (which would be the first cell). Ideally I want to not add the customer name in row 1, but add everything below that.

Any ideas?

 

[davedave24]

edit: should be cell.Offset(1,0) , not (0,1)

 

[davedave24]

I've determined that because I was using RowSource to fill the first 2 columns on userform_initialize(), I couldn't then use .AddItem.

I'm now filling the listbox first 2 columns like this in userform_initialize()

    Dim rngSource As Range
    
    With ThisWorkbook.Sheets("ProductList")
        Set rngSource = .Range(.Range("A2"), _
       .Cells(Rows.Count, 1).End(xlUp)) _
        .Resize(, 2)
    End With
    
    'Fill the listbox
    listProducts.List = rngSource.Cells.Value

It now gets past the .additem line in the other code, and stops on the next line (.List(.Listcount....) with 'Invalid Property value'

 

[davedave24]

And this code should fill column3 of the listbox with that customer's prices, but instead it only takes column C of the spreadsheet into column3 for some reason:

Dim rngFind As Range
    
    With ThisWorkbook.Sheets("ProductList").Range("A1:EE1")
        Set rngFind = .Find(comboCustomer, , xlValues)
    End With
    Set rngFind = rngFind.Offset(1, 0)
    
    Dim rngSource As Range
    
    With ThisWorkbook.Sheets("ProductList")
        Set rngSource = .Range(.Range(rngFind.Address), _
       .Cells(Rows.Count, 1).End(xlUp)) _
       .Resize(, 3)
    End With
    
    'Fill the listbox
    listProducts.List = rngSource.Cells.Value