Listbox populate with two combo date and time format

A

asyamonique

Well-known Member
Joined
Jan 29, 2008
Messages
1,286
Office Version
  1. 2013
Platform
  1. Windows
Code: 
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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
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.

Code: 
    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!
 
Upvote 0
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.
 
Upvote 0
Try adding the following lines in red. Note that I've assumed your textbox is named "TextBox1". Change the name accordingly.

Code: 
    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!
 
Upvote 0
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.
 
Upvote 0
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


Code: 
 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
 
Last edited:
Upvote 0
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
 
Upvote 0
I just found out that the results are not showing by first row cos my datas does't have headers.
Click to expand...

Can you simply add headers?

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

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

Code: 
[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.
 
Upvote 0
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.


[TABLE="width: 652"]
<tbody>[TR]
[TD]MONTH[/TD]
[TD]TITLE[/TD]
[TD]NAME AND SURNAME[/TD]
[TD]DATE[/TD]
[TD]TIME[/TD]
[TD]LOCATION TO[/TD]
[TD]PRICE[/TD]
[TD]DETAILS[/TD]
[TD]VOUCVER NO[/TD]
[TD]REF[/TD]
[/TR]
[TR]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]data[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD][/TD]
[TD]data[/TD]
[TD][/TD]
[TD][/TD]
[TD]data[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD][/TD]
[TD]data[/TD]
[TD][/TD]
[TD][/TD]
[TD]data[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD][/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD][/TD]
[TD]data[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD][/TD]
[TD][/TD]
[TD]data[/TD]
[TD][/TD]
[TD]data[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD][/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD][/TD]
[TD]data[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD][/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD][/TD]
[TD]data[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD][/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD][/TD]
[TD]data[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD][/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD][/TD]
[TD]data[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD][/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD][/TD]
[TD]data[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD][/TD]
[TD][/TD]
[TD]data[/TD]
[TD][/TD]
[TD]data[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]data[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]data[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]data[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]data[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]data[/TD]
[TD]data[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
Try replacing...

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

with

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

Does this help?
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
doesn't show data in listbox on form without any error in this code
Replies
6
Views
117
Abdo
A
I
  • Question Question
Listbox not being populate when code is run
Replies
4
Views
643
ipbr21054
I
P
  • Question Question
ListBox Click to textbox & delete row
Replies
0
Views
607
pure vito
P
sharky12345
  • Solved
Error trying to populate Userform Listbox
Replies
6
Views
600
RoryA
RoryA
I
  • Solved
Populate second ListBox from first Listbox values
Replies
4
Views
534
ipbr21054
I

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top