Add additional column to a listbox in form.

[14spar15]

I’m trying to add a 3rd column to a listbox that is in a form. This 3rd column will be data from column #4 on my sheet. Here is the original code that works fine with 2 columns in the listbox (labeled “Original”). I tried to modify the coding (See “New”) but the 3rd column is not being brought in to the listbox. If I experiment with the numbers here

strList(i, 1) = Cells(i + 2, 2).Value
strList(i, 0) = Cells(i + 2, 3).Value
strList(i, 2) = Cells(i + 2, 4).Value 'This line has been added to original code

I can see the data in the listbox from column 4 (on sheet) but I then loose either column 2 or 3 (From Sheet). Basically it seems a 3rd column is not being brought into the listbox. I hope this all makes sense.

***Original ***

Private Sub UserForm_Initialize()
'this will populate listbox1 with descriptions

Dim strList() As String
Dim intTitleColIndex As Integer
Dim intLastRow As Integer
Dim i As Integer, j As Integer

'find where Title column is on sheet (note: should be column 2)
For i = 1 To 5
If Cells(1, i).Value = "Item Title" Then
intTitleColIndex = i
Exit For


End If

Next

'reset variable length array to total number of descriptions in col 2
intLastRow = WorksheetFunction.CountA(Sheet1.Columns(intTitleCo lIndex))
ReDim strList(intLastRow, intLastRow)

'load array with all descriptions(this may be unneccessary but am doing it incase i need to manipulate array info later)
For i = 0 To intLastRow
'using i to indicate array position as well as row in description column (should always be off by 2)
strList(i, 1) = Cells(i + 2, 2).Value
strList(i, 0) = Cells(i + 2, 3).Value


Next

'load listbox with array strings from title column

With ListBox1
.ColumnHeads = False
.ColumnCount = 2
.ColumnWidths = "50;50"
For i = 0 To intLastRow
.AddItem strList(i, 0)
.List(ListBox1.ListCount - 1, 1) = strList(i, 1)
Next
End With

End Sub


***New ***

Private Sub UserForm_Initialize()
'this will populate listbox1 with descriptions

Dim strList() As String
Dim intTitleColIndex As Integer
Dim intLastRow As Integer
Dim i As Integer, j As Integer

'find where Title column is on sheet (note: should be column 2)
For i = 1 To 5
If Cells(1, i).Value = "Item Title" Then
intTitleColIndex = i
Exit For
End If

Next

'reset variable length array to total number of descriptions in col 2
intLastRow = WorksheetFunction.CountA(Sheet1.Columns(intTitleCo lIndex))
ReDim strList(intLastRow, intLastRow)

'load array with all descriptions(this may be unneccessary but am doing it incase i need to manipulate array info later)
For i = 0 To intLastRow
'using i to indicate array position as well as row in description column (should always be off by 2)
strList(i, 1) = Cells(i + 2, 2).Value
strList(i, 0) = Cells(i + 2, 3).Value
strList(i, 2) = Cells(i + 2, 4).Value 'This line has been added to original


Next

'load listbox with array strings from title column

With ListBox1
.ColumnHeads = False
.ColumnCount = 3 'This was 2 in original
.ColumnWidths = "150;150;150" 'This was .ColumnWidths = "50;50" in original
For i = 0 To intLastRow
.AddItem strList(i, 0)
.List(ListBox1.ListCount - 1, 1) = strList(i, 1)
Next
End With

End Sub

[NoSparks]

While you've added the third column to the array, you've omitted adding it to the listbox

'load listbox with array strings from title column
With ListBox1
    .ColumnHeads = False
    .ColumnCount = 3 'This was 2 in original
    .ColumnWidths = "150;150;150" 'This was .ColumnWidths = "50;50" in original
        For i = 0 To intLastRow
            .AddItem strList(i, 0)
            .List(ListBox1.ListCount - 1, 1) = strList(i, 1)
            .List(ListBox1.ListCount - 1, 2) = strList(i, 2)    '<~~ third column
        Next
End With

but you don't have to do it like that, this should do

'load listbox with array strings from title column
With ListBox1
    .ColumnHeads = False
    .ColumnCount = 3 'This was 2 in original
    .ColumnWidths = "150;150;150" 'This was .ColumnWidths = "50;50" in original
    .List = strList
End With

Have a look at this page, I'm sure it will be of interest.

[14spar15]

Great!!! Thanks so much..