Combo Boxes

[JT]

I have a user form that has 2 combo boxes. 1 is for debits and the other is
for credits. Each form is multi-select.

I am adding 2 labels; one at the top of each combox. I would like to
display the total of all selected in each combobox. If the user selects
another item, the amount displayed increases. Likewise, it the user
de-selects an item, the amount displayed decreases.

There are 6 fields in each combobox. The amount is the 3rd field in the
debit side and the the 4th field in the credit side.

I'm having a little difficulty getting started. How can I tell when an item
as been selected and when it has been de-selected.

Any help would be greatly appreciated. Thanks for the help.......

 

[Dave Peterson]

First, I wouldn't use a combobox for this.

I'd use a listbox. The user can see what they selected--and change their
choices whenever they want.

I built a small userform with a couple of commandbuttons (ok and cancel). A
single listbox and a label. (you can use the same idea with the second listbox
and second label.

Anyway...

This is the code behind the userform:

Option Explicit
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub ListBox1_Change()
Dim iCtr As Long
Dim HowMany As Long
Dim HowMuch As Double

HowMany = 0
HowMuch = 0
With Me.ListBox1
For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) Then
'.list(ictr,2) is the 3rd column in the list
If IsNumeric(.List(iCtr, 2)) Then
HowMany = HowMany + 1
HowMuch = HowMuch + CDbl(.List(iCtr, 2))
End If
End If
Next iCtr
End With

If HowMany = 0 Then
Me.Label1.Caption = ""
Else
Me.Label1.Caption = HowMany & " Selected--Total: " _
& Format(HowMuch, "$#,##0.00")
End If
End Sub
Private Sub UserForm_Initialize()
Dim myRng As Range
With Worksheets("sheet1")
Set myRng = .Range("a2:f" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With

With Me.ListBox1
.ColumnCount = myRng.Columns.Count
.RowSource = myRng.Address(external:=True)
.ColumnHeads = True
.ColumnWidths = "20;20;20;20;20;20"
.MultiSelect = fmMultiSelectMulti
.ListStyle = fmListStyleOption
End With

Me.Label1.Caption = ""

With Me.CommandButton1
.Caption = "Ok"
.Default = True
End With

With Me.CommandButton2
.Caption = "Cancel"
.Cancel = True
End With

Me.Caption = "Make your selection!"
End Sub