How to use AddItem for multiple column ListBox

Skippy

Board Regular
Joined
Mar 3, 2002
Messages
194
Can Additem be used to fill a multiple column listbox from a range in a worksheet? The code below shows what I'm trying to do:

Code:
Sub LoadList()
Dim i As Long
Dim lastRow As Long

With Sheets("Raw data Folio (2)")
    .Activate
    lastRow = .Range("AN65536").End(xlUp).Row
    
    For i = lastRow To 2
        If .Range("AN" & i) <> .Range("AN" & i - 1) Then
            ' Add values to same row of multiple column
            ' listbox for the following cells
            ' A(i)
            ' U(i)
            ' K(i)
            ' etc
        End If
    Next i
End With
    
End Sub
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,061
Office Version
  1. 365
Platform
  1. Windows
From help.
For a multicolumn ListBox or ComboBox, AddItem inserts an entire row, that is, it inserts an item for each column of the control. To assign values to an item beyond the first column, use the List or Column property and specify the row and column of the item.
 

Skippy

Board Regular
Joined
Mar 3, 2002
Messages
194
Norie

I'm unclear on the proper sytnax. Let's say I want to go to the first empty row in the listbox and put values in specified columns
Code:
Sub LoadList() 
Dim i As Long 
Dim lastRow As Long 

With Sheets("Raw data Folio (2)") 
    .Activate 
    lastRow = .Range("AN65536").End(xlUp).Row 
    
    For i = lastRow To 2 Step - 1 
        If .Range("AN" & i) <> .Range("AN" & i - 1) Then 
                test = .Range("A" & i)
                test2 = .Range("U" & i)
                 frmMain.lstSummary.AddItem("test" in column 2)
                 frmMain.lstSummary.Additem("test" in column 4)
         End If 
    Next i 
End With 
    
End Sub
Another option I'm thinking of is to copy values into a contigous range on the ws and use the RowSource property.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,061
Office Version
  1. 365
Platform
  1. Windows
Skippy

I think help is pretty clear.

You use AddItem to add a new row and populate the first column, you then use other methods to populate the other columns.
 

Skippy

Board Regular
Joined
Mar 3, 2002
Messages
194
Norie said:
Skippy

I think help is pretty clear.
Not for a novice like myself :oops: . But I finally, figured it out (after trying many permutations):
Code:
    frmMain.lstSummary.AddItem
    frmMain.lstSummary.List(0, 1) = "Converter"
    frmMain.lstSummary.List(0, 2) = "Stock/Make"

Thanks :biggrin:
 

Watch MrExcel Video

Forum statistics

Threads
1,118,763
Messages
5,574,096
Members
412,567
Latest member
mm1
Top