Listbox header with additem instead rowsource

[drag1c]

Hi all,

I am using code below to get data from sheet into listbox:

Private Sub UserForm_Initialize()
    Dim dataRange As Range
    Dim oneCell As Range
    Dim i As Long
    
    With Sheet8
        Set dataRange = Range(.Cells(Rows.Count, 1).End(xlUp), .Range("O1"))
    End With

    lstDatabase.ColumnCount = dataRange.Columns.Count - 1
    lstDatabase.List = dataRange.Resize(1, dataRange.Columns.Count - 1).Value
    
    lstDatabase.ColumnHeads = True
        
    lstDatabase.ColumnWidths = "10,15,55,55,60,60,45,55,55,55,55,55,55"
    
 If Sheet6.Range("B9") = "Logistics" Then
    For Each oneCell In dataRange.Columns(15).Cells
        If oneCell.Value = "Logistics" Then
            With oneCell.EntireRow
                lstDatabase.AddItem .Cells(1, 1).Value
                For i = 1 To lstDatabase.ColumnCount - 1
                    lstDatabase.List(lstDatabase.ListCount - 1, i) = .Cells(1, i + 1).Value
                Next i
            End With
        End If
    Next oneCell
 ElseIf Sheet6.Range("B9") = "Direct Purchasing" Then
    For Each oneCell In dataRange.Columns(15).Cells
        If oneCell.Value = "Direct Purchasing" Then
            With oneCell.EntireRow
                lstDatabase.AddItem .Cells(1, 1).Value
                For i = 1 To lstDatabase.ColumnCount - 1
                    lstDatabase.List(lstDatabase.ListCount - 1, i) = .Cells(1, i + 1).Value
                Next i
            End With
        End If
    Next oneCell
 ElseIf Sheet6.Range("B9") = "Foreign Trade" Then
    For Each oneCell In dataRange.Columns(15).Cells
        If oneCell.Value = "Foreign Trade" Then
            With oneCell.EntireRow
                lstDatabase.AddItem .Cells(1, 1).Value
                For i = 1 To lstDatabase.ColumnCount - 1
                    lstDatabase.List(lstDatabase.ListCount - 1, i) = .Cells(1, i + 1).Value
                Next i
            End With
        End If
    Next oneCell
 Else
     For Each oneCell In dataRange.Columns(15).Cells
            With oneCell.EntireRow
                lstDatabase.AddItem .Cells(1, 1).Value
                For i = 1 To lstDatabase.ColumnCount - 1
                    lstDatabase.List(lstDatabase.ListCount - 1, i) = .Cells(1, i + 1).Value
                Next i
            End With
    Next oneCell
End If
    lstDatabase.RemoveItem 0
End Sub

Unfortunately I have empty row header. Do you have an idea how this could be changed so row header is Sheet Database Range A1:O1 ?
If it is not possible with .AddItem to do it, could someone translate this into RowSource ? - I have no idea how to do it with loop...

Thank you !

 

[Herakles]

What you can do is have a second listbox which only has one row in it which contains the column headers.

You place this listbox directly above the first one.

Set the Enabled property of the header listbox to False so that the user cannot select it.

Doing it this way also means that the header row is always visible when the user scrolls the listbox
containing the data.

 

[drag1c]

@Herakles this was seems fine, but the problem is I have multiple columns and horizontal scrollbar.

 

[RoryA]

I'd suggest you filter the source range then copy the visible cells to another worksheet, and you can then use the address of that range (excluding the header row) as the rowsource.

 

[drag1c]

I've found one more problem. I can't make way to edit data in sheet, which is listed in listbox via .additem

While I was working with RowSource, it allowed me to use code above without error:

    Me.txtRowNumber.Value = Application.WorksheetFunction.Match(Me.lstDatabase.List(Me.lstDatabase.ListIndex, 0), _
    ThisWorkbook.Sheets("Database").Range("A:A"), 0)

Now, this parameter makes errors. I've tried and Application.Match but it's the same...

At end, so much chaos when using .AddItem instead .RowSource
Could all of this be done with RowSource?