Search code examples
excelvbatimesumlistbox

Get Total Time from a ListBox Column Display per ComboBox Change

I have here a snippet of code for ComboBox4 change event. As you can see in the data below (excel image form), there’s a column for Time which has my preferred format of “hh:mm:ss”. I am trying to get the sum of the time column in ListBox (result shown in Label1 of the Form). The result below is not getting the correct sum.

Form

Form

Excel Sheet Image (blanks have purpose) Sheet Image

Here is the raw data of the image above:

Col. A         Col. B      Col. E       Col. G    Col. J           Col. L
YEAR      || NAME   || Total Time   || COLOR    || MONTH        || SHAPE
2023      || LINA   || 0:00:15      || GREEN    || AUGUST       || HEART
2023      || LINA   || 0:00:07      || GREEN    || SEPTEMBER    || CIRCLE
2024      || GARY   || 0:00:01      || GREEN    || SEPTEMBER    || DIAMOND
2024      || GARY   || 0:00:02      || GREEN    || SEPTEMBER    || RECTANGLE
2024      || GARY   || 0:00:15      || RED      || AUGUST       || OVAL
2023      || GARY   || 0:00:07      || RED      || AUGUST       || RECTANGLE
2023      || GARY   || 0:00:01      || GREEN    || AUGUST       || SQUARE
2024      || GARY   || 0:00:02      || GREEN    || SEPTEMBER    || STAR
2024      || TOM    || 0:00:15      || RED      || AUGUST       || HEART
2024      || TOM    || 0:00:07      || RED      || SEPTEMBER    || CIRCLE
2024      || TOM    || 0:00:01      || RED      || SEPTEMBER    || DIAMOND
2024      || TOM    || 0:00:02      || YELLOW   || SEPTEMBER    || OVAL
2024      || TOM    || 0:00:15      || YELLOW   || OCTOBER      || RECTANGLE
2024      || TOM    || 0:00:07      || YELLOW   || OCTOBER      || CIRCLE
2024      || TOM    || 0:00:01      || YELLOW   || OCTOBER      || SQUARE
2024      || TOM    || 0:00:02      || YELLOW   || OCTOBER      || STAR
2024      || TOM    || 0:00:15      || YELLOW   || OCTOBER      || STAR
2024      || TOM    || 0:00:07      || BLUE     || OCTOBER      || SQUARE

Here is the ComboBox4 code:

Option Explicit
Private Sub ComboBox4_Change()
    If Not ComboBox4.Value = "" Then
        Dim ws As Worksheet, rng As Range, count As Long, K As Long
        Dim arrData, arrList(), i As Long, j As Long
        Set ws = Worksheets("Sheet1")
        
        Dim countT As Date 'declared the variable here
        
        Set rng = ws.Range("A1:L" & ws.Cells(Rows.count, "B").End(xlUp).Row)
        arrData = rng.Value
        count = WorksheetFunction.CountIfs(rng.Columns(1), CStr(ComboBox2.Value), rng.Columns(2), ComboBox1.Value, rng.Columns(7), ComboBox3.Value, rng.Columns(10), ComboBox4.Value)
        ReDim arrList(1 To count + 1, 1 To UBound(arrData, 2))
        For j = 1 To UBound(arrData, 2)
            arrList(1, j) = arrData(1, j) 'header
        Next
        K = 1
                
        For i = 2 To UBound(arrData)
            If arrData(i, 2) = ComboBox1.Value And arrData(i, 1) = CStr(ComboBox2.Value) _
                And arrData(i, 7) = ComboBox3.Value And arrData(i, 10) = ComboBox4.Value Then
                K = K + 1
                
                countT = 0
                
                For j = 1 To UBound(arrData, 2)
                
                    countT = countT + arrData(i, 5) 'trying to get their total sum
                    
                    arrList(K, 5) = Format(arrData(i, 5), "hh:mm:ss")
                Next
                Label1.Caption = Format(CDate(countT), "hh:mm:ss") 'show total sum in this label in the form of hh:mm:ss
            End If
        Next
        With Me.ListBox1
            .ColumnHeads = False
            .ColumnWidths = "0,0,0,0,40,0,0,0,0,0,0,0"
            .ColumnCount = UBound(arrData, 2)
            .List = arrList
        End With
    End If
End Sub

Thank you in advance...

Solution
    • For j = 1 To UBound(arrData, 2) should be eliminated from the nested For loop.
    • Initialize countT before entering the For loop, and update label1 after exiting the loop.

    If you are curious about how the time 0:03:00 is determined, here is the explanation with your code:

    • The variable countT is initialized, and the label is updated within the outer nested loop (for i loop).
    • The value displayed on the label corresponds to the last value that meets the if condition, which is "0:0:15."
    • The inner loop (for j loop) runs 12 times (UBound(arrData,2) is 12), so countT accumulates to 180 seconds (12 * 15), which is equivalent to "0:03:00"
    Option Explicit
Private Sub ComboBox4_Change()
    If Not ComboBox4.Value = "" Then
        Dim ws As Worksheet, rng As Range, count As Long, K As Long
        Dim arrData, arrList(), i As Long, j As Long
        Set ws = Worksheets("Sheet1")
        Dim countT As Date 'declared the variable here
        Set rng = ws.Range("A1:L" & ws.Cells(Rows.count, "B").End(xlUp).Row)
        arrData = rng.Value
        count = WorksheetFunction.CountIfs(rng.Columns(1), CStr(ComboBox2.Value), rng.Columns(2), ComboBox1.Value, rng.Columns(7), ComboBox3.Value, rng.Columns(10), ComboBox4.Value)
        ReDim arrList(1 To count + 1, 1 To UBound(arrData, 2))
        For j = 1 To UBound(arrData, 2)
            arrList(1, j) = arrData(1, j) 'header
        Next
        K = 1
        countT = 0 ' ** Add
        For i = 2 To UBound(arrData)
            If arrData(i, 2) = ComboBox1.Value And _
                arrData(i, 1) = CStr(ComboBox2.Value) And _
                arrData(i, 7) = ComboBox3.Value And _
                arrData(i, 10) = ComboBox4.Value Then
                K = K + 1
                ' countT = 0 ' ** Remove
                ' For j = 1 To UBound(arrData, 2) ' ** Remove
                countT = countT + arrData(i, 5) 'trying to get their total sum
                arrList(K, 5) = Format(arrData(i, 5), "hh:mm:ss")
                ' Next ' ** Remove
                ' Label1.Caption = Format(CDate(countT), "hh:mm:ss") ' ** Remove
            End If
        Next
        Me.Label1.Caption = Format(CDate(countT), "hh:mm:ss") 'show total sum in this label in the form of hh:mm:ss
        With Me.ListBox1
            .ColumnHeads = False
            .ColumnWidths = "0,0,0,0,40,0,0,0,0,0,0,0"
            .ColumnCount = UBound(arrData, 2)
            .List = arrList
        End With
    End If
End Sub