Filter listbox data with specific value, sum the result and show in textbox

[Rolsu]

Hi all!

I have a issue with my register.
I have data in a 4 column listbox. In the fourth I have hours and in the third I have the worktype (can be two types). I'd like to have the sum hours of the two types and show them in two textboxes.
I tried to filter the listbox and sum the selected data but in this case the listbox content always changed and I don't want that.
So I moved to a differnt method.
I have a code that filters the data on a sheet where the listbox gets the data. I also have a function in a standard module that sums the visible cells. I'd like to call the function in the filter code but can't refer properly.
Can someone help?
Or if someone can offer me an easier solution that's ok too.
Thanks.

Filter code:

    Dim Code As String
    Dim myDB As Range

    If TextBox1.Value < 0 Then Exit Sub
        Code = TextBox1.Value

    With Sheets("Data")
        Set myDB = Sheets("Data").Range("A1:D1").Resize(.Cells(.Rows.Count, 1).End(xlUp).Row)
    End With

    With myDB
        .AutoFilter 'remove filters
        .AutoFilter field:=1, Criteria1:=Code  'filter data
        .AutoFilter field:=3, Criteria1:="Design"
        Call SumVisible(???????) 'here I always get the ByRef argument error
        .AutoFilter
    End With

Sum code:

Function SumVisible(WorkRng As Range) As Double
    
    Dim rng As Range
    Dim total As Double

        For Each rng In WorkRng
            If rng.Rows.Hidden = False And rng.Columns.Hidden = False Then
            total = total + rng.Value
            End If
        Next
        
    TextBox2.Value = total
    
End Function

 

[Micron]

You have the listbox list items coming from a filtered range? What about looping over the listbox items along these lines?

If Listbox1.column(2,i) = "type1" then 
   Textbox1 = Textbox1 +Listbox1.Column(3,i)
Else 'or do a specific test for "type2"
   Textbox2 = Textbox2 + Listbox1.Column(3,i)
End If

Column property is zero based, so column 2 is the 3rd column.

 

[Rolsu]

You have the listbox list items coming from a filtered range? What about looping over the listbox items along these lines?

If Listbox1.column(2,i) = "type1" then
   Textbox1 = Textbox1 +Listbox1.Column(3,i)
Else 'or do a specific test for "type2"
   Textbox2 = Textbox2 + Listbox1.Column(3,i)
End If

Column property is zero based, so column 2 is the 3rd column.

Hi Micron!

Sorry for the late reply.
I've tried your code but can't get the sum value of type1, only just the header of the 4th column...

 

[Micron]

I don't understand what you're saying, especially since I can't see what you see. My suggestion was to loop over the listbox items instead of the sheet. If you used exactly what I posted, that is not a loop; it's how to get at the column values. However, I think it matters if your listbox is on a sheet or userform. I seem to be able to get at column values when on a form, but not when on a sheet but you have not disclosed where yours is.

 

[Rolsu]

I don't understand what you're saying, especially since I can't see what you see. My suggestion was to loop over the listbox items instead of the sheet. If you used exactly what I posted, that is not a loop; it's how to get at the column values. However, I think it matters if your listbox is on a sheet or userform. I seem to be able to get at column values when on a form, but not when on a sheet but you have not disclosed where yours is.

I don't use any userform, my list-and textboxes are on a worksheet.
And yes, the problem was with the direct use of your code. I did the loop but forgot to change some things.
After the modifications it works perfectly.
Here is the full code:

    Dim i As Integer
    Dim total As Double
       
    With ListBox1
        For i = 0 To .ListCount - 1
            If ListBox1.Column(2, i) = "Design" Then
                total = total + ListBox1.Column(3, i)
            End If
        Next i
    End With
   
    TextBox2.Value = total

Thank you for the help!

 

[Micron]

Glad I was able to help & thanks for the recognition.