PDA

View Full Version : [SOLVED] Listbox - calculation based on selections



AnnieM
02-05-2017, 03:24 AM
Hi All,

I have a ListBox (lstBox1) on a userform that contains 12 items ("1" - "12")

Each of these items is related to a TextBox on the form ("TextQty1" - "TextQty12")

I am trying to get the sum of the corresponding values of "TextQty" selected from the ListBox items so that I can use this total in a sub elsewhere on the form. I am thinking something like:

Total = 0
If ListIndex0.Selected Then Total = Total + TextQty1.Value
If ListIndex1.Selected Then Total = Total + TextQty2.Value


But I really don't know how to structure this or if there is a better way, so can anyone please offer some advice?

Thanks in advance,

Anne

MickG
02-05-2017, 05:25 AM
Perhaps this:-
Ensuring the Listbox Property is set at "Multiselect"


Private Sub CommandButton1_Click()
Dim Tot As Double, n As Long
With ListBox1
For n = 0 To .ListCount - 1
If .Selected(n) Then
Tot = Tot + Me.Controls("TextBox" & n + 1).Object.Value
End If
Next n
End With
MsgBox Tot
End Sub


Regards Mick

snb
02-05-2017, 05:45 AM
Private Sub UserForm_Click()
For j = 0 To ListBox1.ListCount - 1
y = y + ListBox1.List(j) * Abs(ListBox1.Selected(j))
Next

MsgBox y
End Sub

AnnieM
02-05-2017, 05:49 AM
Hi Mick,

That worked perfectly.

Thank you for your help, it is greatly appreciated and has saved me hours of frustration.

Regards,

Anne

MickG
02-05-2017, 06:42 AM
You're welcome