Dynamic total on listbox selection 1

[WelshyWizard]

Hi there,

I'm not too sure if this can be done but this is what I'm looking for.

I've got a listbox on a userform. The listbox has the frmMultiSelectExtended option selected. The listbox is also multicolumn.
One of the columns holds a numerical value which I want summed in a textbox underneath the listbox.

The listbox gets its values from a worksheet.

I want the user to be able to select whichever rows they want and for the total at the bottom to change depending upon what they have select. If nothing is selected then all values should be summed....

Any ideas? I'm struggling on this one!

Cheers


Today is the tomorrow you worried about yesterday - and all is well.....

 

[firefytr]

Hi,

Something like this ...

Private Sub ListBox1_Change()
    Dim i As Long, dblSum As Double, dblTotal As Double, blnSelect As Boolean
    blnSelect = False
    For i = 0 To Me.ListBox1.ListCount - 1
        dblTotal = dblTotal + Me.ListBox1.List(i, 1)
        If Me.ListBox1.Selected(i) Then
            blnSelect = True
            dblSum = dblSum + Me.ListBox1.List(i, 1)
        End If
    Next i
    If blnSelect = False Then
        Me.lblSum.Caption = Format(dblTotal, "#,##0")
    Else
        Me.lblSum.Caption = Format(dblSum, "#,##0")
    End If
End Sub

Private Sub UserForm_Initialize()
    Dim i As Long, dblTotal As Double
    Me.ListBox1.List = Sheets("Sheet1").Range("A1:B10").Value
    For i = 0 To Me.ListBox1.ListCount - 1
        dblTotal = dblTotal + Me.ListBox1.List(i, 1)
    Next i
    Me.lblSum.Caption = Format(dblTotal, "#,##0")
End Sub

Note that I changed the name of the text box control to lblSum, so change that to whatever textbox you want the value displayed in.

HTH

-----------
Regards,
Zack Barresse

Simplicity is the ultimate sophistication.
- Leonardo da Vinci

 

[WelshyWizard]

Cheers Zack,

Could you possibly explain to me what this code is doing? The value I want to total is actually in column 4 of my listbox. How do I reference this?

Thanks

Today is the tomorrow you worried about yesterday - and all is well.....

 

[firefytr]

The column is the second number here ...

Me.ListBox1.List(i, 1)

So column 1 would be 0, column 4 would be 3.

Private Sub ListBox1_Change()

    'dimension variables
    Dim i As Long, dblSum As Double, dblTotal As Double, blnSelect As Boolean
    
    'set boolean check to false
    blnSelect = False
    
    'iterate through listbox total items
    '(listbox index starts at 0 and goes up)
    For i = 0 To Me.ListBox1.ListCount - 1
    
        'Add each item together and get a total
        dblTotal = dblTotal + Me.ListBox1.List(i, 1)
        
        'Check if the item is selected or not
        If Me.ListBox1.Selected(i) Then
        
            'set our boolean/check to true
            blnSelect = True
            
            'Add only selected items together
            dblSum = dblSum + Me.ListBox1.List(i, 1)
            
        End If
        
    Next i
    
    'Check our boolean value
    If blnSelect = False Then
        
        'Change caption to the selected sum values
        Me.lblSum.Caption = Format(dblTotal, "#,##0")
        
    Else
    
        'Change caption to all of the added values (if no items selected)
        Me.lblSum.Caption = Format(dblSum, "#,##0")
        
    End If
    
End Sub

Private Sub UserForm_Initialize()

    'Dimension variables
    Dim i As Long, dblTotal As Double
    
    'Set the listbox value to sheet value (range should equal size of listbox)
    Me.ListBox1.List = Sheets("Sheet1").Range("A1:B10").Value
    
    'Iterate through the listbox
    For i = 0 To Me.ListBox1.ListCount - 1
    
        'Add all totals together
        dblTotal = dblTotal + Me.ListBox1.List(i, 1)
        
    Next i
    
    'Change caption to total values
    Me.lblSum.Caption = Format(dblTotal, "#,##0")
    
End Sub

HTH

-----------
Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a Microsoft MVP?
- Leonardo da Vinci

 

[WelshyWizard]

Thanks again.

When I use this code I get the error 'Type mismatch' on this line of code:

dblTotal = dblTotal + Me.ListBox1.List(i, 4)

Any ideas?

Today is the tomorrow you worried about yesterday - and all is well.....

 

[firefytr]

Remember, column 4 should be labeled as a 3.

dblTotal = dblTotal + Me.ListBox1.List(i, 3)

It's confusing, I know.

-----------
Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a Microsoft MVP?
- Leonardo da Vinci

 

[WelshyWizard]

Brilliant! Sorry, I missed that bit!

Works just as I want!

Thanks for all your help!

Today is the tomorrow you worried about yesterday - and all is well.....