Populate listbox row by row, depenadble of cell values

[drag1c]

Hi,

for example, there is a table A1:D10 (headers included).
In column D:D are values:
1. Logistics
2. Direct Purcashing

If I have login/logout for each user in excel separate sheet (like database), how I can make "If statement" for loop to populate listbox with data by D column?
The truth is I dont want to use two different sheets for this job. I would like to use one table as database.

Something like:
For Each i In Sheets("Database").Range("H3", Sheets("Database").Range("H" & Application.Rows.Count).End(xlUp))
If ..... Then
... .RowSource = ...
End If...

Next i

Is that possible?

 

[shinigamilight]

If I understood you correctly this is what you're after.

Private Sub Worksheet_Change(ByVal Target As Range)
        ListBox1.List = Sheets("Sheet1").ListObjects("Table1").ListColumns(4).DataBodyRange.Value
End Sub

 

[drag1c]

Um, not exactly.
here is more detailed:

iRow = WorksheetFunction.CountA(Sheets("Database").Range("A:A"))
.
.
.
With TaskManager
.lstDatabase.ColumnCount = 13
        .lstDatabase.ColumnHeads = True
        .lstDatabase.ColumnWidths = "10,15,55,55,60,60,45,55,55,55,55,55,55"
        If iRow > 1 Then
            .lstDatabase.RowSource = "Database" & "!A2:N" & iRow
        Else
            .lstDatabase.RowSource = "Database" & "!A2:N2"
        End If
End With

This is part of the code which I use now for row population in listbox. I would like to change it to be dependable of Area column (Column 3 in listbox). In separate sheet I have:

By this table I check cell B9 and then if there is written "Logistics" or "Direct Purchasing" I want to populate just these rows in listbox (comparing B9 to Database column 3 for example)
In department could be written both, so it has to be "Find", something like

If Instr(......)

 

[drag1c]

Found a way:

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
    
 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

This code looks for dataRange in column O:O, afterwards it take values from that column and compare data which will be shown in the listbox named lstDatabase.
Good luck if anyone needs this code