Listbox populate with two combo date and time format

[asyamonique]

Application.ScreenUpdating = False

Dim r As Long, Ac As Long, c As Long
  If ComboBox10.Value <> -1 And ComboBox11.Value <> -1 Then
        ListBox2.Clear
        With Worksheets("taxibyname").Range("A1:A5000")
           ReDim Ray(1 To .Count, 1 To 11)
            For r = 1 To .Rows.Count
               With ListBox2
                    .ColumnCount = 11
                    .ColumnWidths = "0;80;80;200;200;140;120;100;60;0;0"
                End With
                If ComboBox10.Value = .Cells(r, 1).Value And ComboBox11.Value = .Cells(r, 2).Value Then
                   c = c + 1
                   For Ac = 1 To 11
                        Ray(c, Ac) = .Cells(r, Ac)
                   Next Ac
                End If
            Next r
        End With
End If
ListBox2.List = Ray


Application.ScreenUpdating = True

Hello,
Could you please help me with that code given above?
Column C "Date"and D "hrs" entries, I need to format that columns at listbox as "dd-mmm-yyyy" and "h:mm"
Also the progres gets long time to populate listbox is there any solution to fix that problem to make it shorter?
Many thanks

 

[Domenic]

The following macro uses the AutoFilter to filter the data based on the two combobox values, copies the filtered data to a worksheet, let's say Sheet2, and then assigns the result to the RowSource property of the listbox. Also, I would suggest setting the ColumnCount and ColumnWidths properties within the Initialize event for the userform.

    Dim wsData As Worksheet
    Dim wsResults As Worksheet
    Dim rFilt As Range


    Application.ScreenUpdating = False
    
    Set wsData = Worksheets("taxibyname")
    Set wsResults = Worksheets("Sheet2")
    
    With ListBox2
        .RowSource = ""
        .ColumnCount = 11
        .ColumnWidths = "0;80;80;200;200;140;120;100;60;0;0"
    End With
    
    wsResults.Cells.ClearContents
    
    If ComboBox10.Value <> "" And ComboBox11.Value <> "" Then
        With wsData
            .AutoFilterMode = False
            With .Range("A1:K" & .Cells(.Rows.Count, "A").End(xlUp).Row)
                .AutoFilter field:=1, Criteria1:=ComboBox10.Value
                .AutoFilter field:=2, Criteria1:=ComboBox11.Value
                On Error Resume Next
                Set rFilt = .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
                On Error GoTo 0
                If Not rFilt Is Nothing Then
                    .Offset(1, 0).Copy wsResults.Range("A1")
                    ListBox2.RowSource = wsResults.Range("A1").CurrentRegion.Address(, , , True)
                End If
                .AutoFilter
            End With
        End With
    End If
    
    Application.ScreenUpdating = True

Hope this helps!

 

[asyamonique]

Domenic ,
Thank you very much.
Is it possible to add a textbox near combo and will give me to sum values of column "F"
By the way I've altered the column amount from 11 to 9. ("A:I")
Many Thanks.

 

[Domenic]

Try adding the following lines in red. Note that I've assumed your textbox is named "TextBox1". Change the name accordingly.

    Dim wsData As Worksheet
    Dim wsResults As Worksheet
    Dim rFilt As Range


    Application.ScreenUpdating = False
    
    Set wsData = Worksheets("taxibyname")
    Set wsResults = Worksheets("Sheet2")
    
    With ListBox2
        .RowSource = ""
        .ColumnCount = 11
        .ColumnWidths = "0;80;80;200;200;140;120;100;60;0;0"
    End With
    
    [COLOR=#ff0000]TextBox1.Value = ""[/COLOR] 'change the name accordingly
    
    wsResults.Cells.ClearContents
    
    If ComboBox10.Value <> "" And ComboBox11.Value <> "" Then
        With wsData
            .AutoFilterMode = False
            With .Range("A1:K" & .Cells(.Rows.Count, "A").End(xlUp).Row)
                .AutoFilter field:=1, Criteria1:=ComboBox10.Value
                .AutoFilter field:=2, Criteria1:=ComboBox11.Value
                On Error Resume Next
                Set rFilt = .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
                On Error GoTo 0
                If Not rFilt Is Nothing Then
                    .Offset(1, 0).Copy wsResults.Range("A1")
                    ListBox2.RowSource = wsResults.Range("A1").CurrentRegion.Address(, , , True)
[COLOR=#ff0000]                    TextBox1.Value = Application.Sum(wsResults.Columns("F"))[/COLOR]
                End If
                .AutoFilter
            End With
        End With
    End If
    
    Application.ScreenUpdating = True

Hope this helps!

 

[asyamonique]

Thank you Domenic,
I just found out that the results are not showing by first row cos my datas does't have headers.First row does not included into the filtered data.
Is there anything we can do for it?
Thanks again.

 

[asyamonique]

Hello Again
I would like to add another issue which is very interesting some how that code now populates first 4 columns is there anything missing with below changes?
Many Thanks

 Dim wsData As Worksheet    Dim wsResults As Worksheet
    Dim rFilt As Range




    Application.ScreenUpdating = False
    
    Set wsData = Worksheets("taxi")
    Set wsResults = Worksheets("taxifiltered")
    
    With ListBox1
        .RowSource = ""
        .ColumnCount = 10
        .ColumnWidths = "0;100;180;100;100;200;120;0;40;0"
    End With
    
    
    wsResults.Cells.ClearContents
    
    If ComboBox9.Value <> "" And ComboBox10.Value <> "" Then
        With wsData
            .AutoFilterMode = False
            With .Range("A1:J" & .Cells(.Rows.Count, "A").End(xlUp).Row)
                .AutoFilter field:=1, Criteria1:=ComboBox9.Value
                .AutoFilter field:=2, Criteria1:=ComboBox10.Value
                On Error Resume Next
                Set rFilt = .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
                On Error GoTo 0
                If Not rFilt Is Nothing Then
                    .Offset(1, 0).Copy wsResults.Range("A1")
                    ListBox1.RowSource = wsResults.Range("A1").CurrentRegion.Address(, , , True)
                    TextBox24.Value = Application.Sum(wsResults.Columns("G"))
                End If
                .AutoFilter
            End With
        End With
    End If
    
    Application.ScreenUpdating = True

 

[asyamonique]

Hi,
I just found out the reason why the listbox populating missing datas by column...
Due to some columns half has no data entries its not showing all as in original list.
So is there any way to fix that issue?
Thanks

 

[Domenic]

I just found out that the results are not showing by first row cos my datas does't have headers.

Can you simply add headers?

Due to some columns half has no data entries its not showing all as in original list.

Sorry, I don't understand the issue. Can you please elaborate? Just in case you are not already aware, some of the columns displayed in the listbox are hidden because their column widths are set to 0, as per the ColumnWidths property...

[COLOR=#333333].ColumnWidths = "0;100;180;100;100;200;120;0;40;0"[/COLOR]

So as you can see, the column width for columns 1, 8, and 10 are set to 0, and so those columns will be hidden in the listbox. So you can simply adjust the column widths as necessary.

 

[asyamonique]

Hi,
Thanks for the reply,
I've add headers already.
So below sample is the what excatly I have data on worksheet.
When its filtered listbox not showing entire informations.
Thank you.

MONTH	TITLE	NAME AND SURNAME	DATE	TIME	LOCATION TO	PRICE	DETAILS	VOUCVER NO	REF
data	data	data	data					data	data
data	data	data	data		data			data	data
data	data	data	data		data			data	data
data	data	data	data		data	data		data	data
data	data	data	data			data		data	data
data	data	data	data		data	data		data	data
data	data	data	data		data	data		data	data
data	data	data	data		data	data		data	data
data	data	data	data		data	data		data	data
data	data	data	data		data	data		data	data
data	data	data	data			data		data	data
data	data	data	data					data	data
data	data	data	data					data	data
data	data	data	data					data	data
data	data	data	data					data	data
data	data	data	data					data	data

<tbody>
</tbody>

 

[Domenic]

Try replacing...

[COLOR=#333333]ListBox1.RowSource = wsResults.Range("A1").CurrentRegion.Address(, , , True)[/COLOR]

with

ListBox1.RowSource = wsResults.UsedRange.Address(, , , True)

Does this help?