Sum a column in listbox??? 1

[JasonPerdue]

Is it possible to sum a column in a listbox and display the result in a textbox?

Something like this:
TextBox = Sum(ListBoxName.Column(1))

 

[Schof]

I think the only way is to iterate through each row and sum the column's value. Put this function in a module and call it from any form, passing in the combobox control and column number that you want to sum.

For Example,

columnTotal = sumListboxColumn(me.listboxName, 1)

Public Function sumListboxColumn(lstbox As Control, columnNumber As Integer) As Integer
On Error GoTo Err_sumListboxColumn

Dim counter As Integer
Dim total As Integer

total = 0
For counter = 0 To lstbox.ListCount - 1
total = total + lstbox.Column(columnNumber, counter)
Next counter

Exit_sumListboxColumn:
sumListboxColumn = total
Exit Function

Err_sumListboxColumn:
MsgBox Err.Description
Resume Exit_sumListboxColumn

End Function

 

[JasonPerdue]

Thanks for the response!

Your function worked great after resolving a data type mismatch within my code.

Nice, clean, and concise - I appreciate it.

After much trial and error with other methods, I was able to get the desired result with DSum and passing it the same criteria as the RowSource of the listbox. If anyone is interested I'd be happy to post an example.

 

[jedel]

SCHOF

Good post, take a star

I'd give my right arm to be ambidextrous!