Showing total value of column in a multiselect list box in a text

[Halfbrain]

Hi,
I have a list box that has two column's, Column one is a recordID and column
two has a dollar value.

I want to have a label or textbox on the same form as the list box that
shows the total value (sum) of all the values in the second column of the
list box

eg

Listbox contents shows
Column 1 Column 2
34 $34.40
65 $27.89
123 $62.50

etc

I am trying to work out how to code so that a text box or label will show
the sum of Column 2. i.e $124.79 but can' t work it out.

The list box is populated by the user selecting records from another listbox
and then cllicking a button to transfer the selected records to the above
list box. They can also remove items from the second (above) list box, I
therefore need the total text box to recalculate each time an item is added
or removed from the listbox.

Thanks in advance for any help or guidance

Steve

 

[Rui]

Try this code by adding a event on the listbox click event

Private Sub MYLISTBOX_Click()
Dim displaySum As Double
Dim varItem
For Each varItem In MYLISTBOX.ItemsSelected
displaySum = displaySum + MYLISTBOX.Column(1, varItem)
Next varItem
MsgBox displaySum
End Sub

Remarcs:
replace MYLISTBOX with the name of your listbox


cheers
Rui

 

[Halfbrain]

Hi Rui,

Thanks for your proposed solution. It is pretty much similar to where I
already was unfortunately. I actually had similar code assigned to the
afterupdate event of the listbox. Assigning the code to the click event (as
in your suggestion) doesn't really work as I would like becasue I want it to
recalc whenever there is a change to the listbox contents, and that can
happen when a record is added by the user clicking on the items in the other
list box or clicking on an add all button etc. I also have the result (ie.e
displaysum) going to the caption paramater of a list box rather than the
suggested msgbox. I don't want a msgbox popping up every time a user clicks
the lists.

Your solution is good and does work ( with label.caption rather than msgbox
as mentioned above) but I guess I need to find the right event trigger to
update whenever the contents of the listbox changes rather than your
suggestion of using the listbox onclick event.

Again,
Thanks for your suggestion
Steve