Populate Multi Column List Box

[JackMoorhead]

Good morning to all,
I am trying to populate a multi column listbox but keep running into an error that must be caused by my code.

I don't understand how the properties are for a multi column listbox and I am looking for a resource to learn how.
So far I know how to use a single column list box but am stuck on populating a multi column listbox.

Would anyone direct me to a good tutorial, or help me with my error?

Thanks in advance,
Jack

My code is:

Private Sub PoplstDistCenters()
Dim LastRow As Long
Dim tCol As Integer
Dim tRow As Long
Dim lstColWid As Integer
Dim tStr As String
Dim iDC_Ct As Integer
Dim lstCount As Integer

tRow = 2
lstColWid = 25
tStr = ""
LastRow = Sheets("SC").Range("A65536").End(xlUp).Row
iDC_Ct = 0
lstCount = 0

Me.lstDistCenters.Clear

Sheets("SC").Activate

'------------------------------------
' set the column widths
For tCol = 1 To Me.lstDistCenters.ColumnCount - 1
If tCol = 3 Then
'If tCol = 2 Or tCol = 3 Then
tStr = tStr & "0 pt; "
Else
tStr = tStr & lstColWid & " pt; "
End If
Next tCol
tStr = tStr & lstColWid & " pt"

Me.lstDistCenters.ColumnWidths = tStr
'------------------------------------
' define the labels
Me.lblDistCenters.Caption = _
" DC SC Dolly ONH ENR O/S POOL TRAC ALL"
'------------------------------------
' populate the listbox
'---------DCs on the top row---------
For tRow = 2 To LastRow - 1
If Sheets("SC").Cells(tRow, 1).Value = Int(Me.cboDCs.Value) Then
With Me.lstDistCenters
.AddItem Sheets("SC").Cells(tRow, 1).Value
iDC_Ct = iDC_Ct + 1
lstCount = lstCount + 1
End With
End If
lstCount = lstCount + 1
Next tRow
'-------SCs to follow after DC-------
For tRow = 2 To LastRow - 1
If (Sheets("SC").Cells(tRow, 2).Value = Int(Me.cboDCs.Value)) _
And (Sheets("SC").Cells(tRow, 1).Value <> Int(Me.cboDCs.Value)) Then
Me.lstDistCenters.AddItem Sheets("SC").Cells(tRow, 1).Value
iDC_Ct = iDC_Ct + 1
lstCount = lstCount + 1
End If
Next tRow
'-----------------------------------------
'=========================================
' My attempt to multi column listbox------
' For tRow = 2 To LastRow - 1
' If (Sheets("SC").Cells(tRow, 2).Value = Int(Me.cboDCs.Value)) _
' Or (Sheets("SC").Cells(tRow, 1).Value <> Int(Me.cboDCs.Value)) Then
' With Me.lstDistCenters
' .AddItem
' .List(tRow, 1) = Sheets("SC").Cells(tRow, 1).Value
' .List(tRow, 2) = Sheets("SC").Cells(tRow, 1).Value
' .List(tRow, 3) = Sheets("SC").Cells(tRow, 1).Value
' .List(tRow, 4) = Sheets("SC").Cells(tRow, 1).Value
' .List(tRow, 5) = Sheets("SC").Cells(tRow, 1).Value
' .List(tRow, 6) = Sheets("SC").Cells(tRow, 1).Value
' .List(tRow, 7) = Sheets("SC").Cells(tRow, 1).Value
' iDC_Ct = iDC_Ct + 1
' lstCount = lstCount + 1
' End With
' End If
' Next tRow

'====================================
'------------------------------------
' populate the DC count
Me.txtDC_Ct.Value = iDC_Ct
End Sub

 

[Norie]

What are you having problems with exactly?

 

[JackMoorhead]

Norie,
I can populate single column listboxes. I have a spreadsheet 267 rows and 10 columns that I would like to place into the listbox.
Of course there is some filtering done with IF statements.
I don't know how to get the data from the spreadsheet to the listbox - column by column; row by row.
Thanks for your question.
Jack

 

[JackMoorhead]

Norie,
Thanks for responding with the question. I figured it out myself.
Jack