Listbox not showing proper data

[xsmurf]

Hi,

I'am hoping to get some help here.
I have a userform that needs to show values, if some criteria is met.
please see attached file. ( extra data has been removed )

How it should work:
I fill in the clocknumber and the job is being shown => this works
then I fill in the StartDate & EndDate.
Now when I press "commandbutton1" it should show me results in the listbox => this works too.

The Problem:
It shows info that is not supposed to be there.

It should only show information that is within the criteria.
For example:
If my clocknumber=1 ( job=Painter) and my StartDate= 01/01/2023 and EndDate= 01/13/2023, it should only list all the names that match the criteria and are falling on or in between the Start/EndDate.
In this case only employee 1 / 3/ 5 should be in the list, not all the painters.

Hope someone can assist me with this problem, any help is appreciated.

Also I would not like to see double names in the list if multiple dates are found. The name should be shown once, and then the dates would need to be horizontally next to that name ( now every date is a row) .
Don't know if this is possible.

Thanks again

example file

 

[Anthony47]

The Problem:
It shows info that is not supposed to be there.
It should only show information that is within the criteria.

But you never test that the specific Employee has an activity marked on the date.

Try modifying this line within Private Sub cmb_Filter_Click

                ' Check if the job column (column C) contains the keyword 
                If InStr(1, ws.Cells(row, 3).Value, jobKeyword, vbTextCompare) > 0 And ws.Cells(row, col) <> "" Then    'MODIFY

Also I would not like to see double names in the list if multiple dates are found. The name should be shown once, and then the dates would need to be horizontally next to that name

Could you make an example?

 

[xsmurf]

@Anthony47 This did the trick, I am getting the proper data. Thank you so much.

Please see the attached photo, for an example how I would like it. Don't know if this is possible

Again thank you so much for your help.

 

[Anthony47]

In this case the looping sequence need to be changed: first scan by row then by Date in column 3.
Probably this version:

Private Sub cmb_Filter_Click()
    Dim jobKeyword As String
    Dim startDate As Date
    Dim endDate As Date
    Dim ws As Worksheet
    Dim lastColumn As Long
    Dim AnyDataFound As Boolean
    Dim row As Long
    Dim col As Long
    Dim currentDate As Date
    Dim columnDataFound As Boolean
    Dim rowData As String
Dim IsIn As Boolean
'
    jobKeyword = txt_Job.Value
    startDate = CDate(txt_StartDate.Value)
    endDate = CDate(txt_EndDate.Value)
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    lastColumn = ws.Cells(3, ws.Columns.Count).End(xlToLeft).Column
    AnyDataFound = False
    lst_Results.Clear
    'Scan by rows in column C, then by Date in Row3
    For row = 4 To ws.Cells(ws.Rows.Count, "C").End(xlUp).row
        rowData = ws.Cells(row, 3).Value & vbTab & ws.Cells(row, 4).Value
        IsIn = False
        For col = 10 To lastColumn
            currentDate = ws.Cells(3, col).Value
            If currentDate >= startDate And currentDate <= endDate Then
                ' Check if the job column (column C) contains the keyword
                If InStr(1, ws.Cells(row, 3).Value, jobKeyword, vbTextCompare) > 0 And ws.Cells(row, col) <> "" Then
                    ' Combine the data for each matching entry and add it to the ListBox
                    rowData = rowData & vbTab & Format(currentDate, "dd/mm/yyyy")
                    IsIn = True
                    ' Set the overall data found flag to True
                    AnyDataFound = True
                End If
            End If
        Next col
        If IsIn Then lst_Results.AddItem rowData
    Next row
    '
    If Not AnyDataFound Then
        MsgBox "No matching data found.", vbInformation
    End If
End Sub

Try...

 

[xsmurf]

@Anthony47
Both solutions work like a charm, thank you so much.
I will study the changes in the code, so I know for next time.

Again, Thank you for your help.

 

[xsmurf]

@Anthony47 It seems that if the data retrieval is bigger that the listbox it will not show a horizontal scrollbar. Any thoughts on how to correct this?
Since the listbox data is dynamic setting fix columnwidhts will not be option. ( A option to set the first 2 columnswidths can be down, but the rest depands on the data in the worksheet)

 

[xsmurf]

So i found out that because I use vbTab I only have 1 column, only by setting the column width to 700 I am able to get the horizontal scrollbar.
Would it be possible to get the data into separate columns instead of using vbTab?

 

[Anthony47]

You could switch to a multicolumn listbox, but that require the macro to be rethinked, as we'll know how many columns are needed only at the end of the scans.
An easy way could be setup a column width in excess of the listbbox width.
For example:
1) set a standard column with on starting:

Dim IsIn As Boolean
'
Me.lst_Results.ColumnWidths = Me.lst_Results.Width - 10     '+++ Default width
    jobKeyword = txt_Job.Value

2) then extend that width if a content is "wider than a threshold"

        Next col
        If IsIn Then lst_Results.AddItem rowData
        If Len(rowData) > 100 Then Me.lst_Results.ColumnWidths = 1000   '+++2 Extended width
    Next row

The lines added are marked +++
This set a threshold at 100 charachters, but you need to determine the optimal value (even though a wide column, and thus the scroll bar, should not affect the performances of the solution)

You might want to add a "marker" at the end of the string to be confident that you see all the calculated results; in this case modify, for example:

        If IsIn Then lst_Results.AddItem rowData & " <"

The added code is in Italics

 

[xsmurf]

You could switch to a multicolumn listbox, but that require the macro to be rethinked, as we'll know how many columns are needed only at the end of the scans.
An easy way could be setup a column width in excess of the listbbox width.
For example:
1) set a standard column with on starting:

Dim IsIn As Boolean
'
Me.lst_Results.ColumnWidths = Me.lst_Results.Width - 10     '+++ Default width
    jobKeyword = txt_Job.Value

2) then extend that width if a content is "wider than a threshold"

        Next col
        If IsIn Then lst_Results.AddItem rowData
        If Len(rowData) > 100 Then Me.lst_Results.ColumnWidths = 1000   '+++2 Extended width
    Next row

The lines added are marked +++
This set a threshold at 100 charachters, but you need to determine the optimal value (even though a wide column, and thus the scroll bar, should not affect the performances of the solution)

You might want to add a "marker" at the end of the string to be confident that you see all the calculated results; in this case modify, for example:

        If IsIn Then lst_Results.AddItem rowData & " <"

The added code is in Italics

yes, this works alot better. I still got to fine tune it, but you helped me alot. Thank you.

 

[xsmurf]

@Anthony47

Quick question:
Depanding on the lenght of the names in "ws.Cells(row, 5)" & "ws.Cells(row, 4)" the data doesn't align properly.
Is there a way/option to give the "rowdata" a specific columnwidth, and the rest of the data a seperate columnwidth.
I did some testing but I guess "&vbTab" & columnwidth are not the same.

Hope you can help me out one more time.
Thank you.