PDA

View Full Version : Solved: populate list box with totals



austenr
11-27-2006, 09:59 AM
I have a rather large macro that populates around 10 totals. Can anyone show me how to present these in a list box at the end of a macro? Thanks

CBrine
11-27-2006, 10:01 AM
Austenr,
Is the listbox on a UserForm, or an ActiveX control in the sheet, or a forms control on the sheet? Please provide the name of the control as well.(And the UserForm if necessary)

Cal

austenr
11-27-2006, 10:10 AM
Don't have the user form yet. You can just use the one in the VBE as an example if you want. That is what I was going to use.

CBrine
11-27-2006, 11:04 AM
How do you want to key the update of the listbox? On the initialization of the form, a button based refresh, a textbox exit, or on selecting the actual listbox?

lucas
11-27-2006, 11:05 AM
Come on austenr......don't make us create a workbook trying to figure out what your doing just to have you come back and say...no thats not what I'm trying to do. Please give us a sample workbook with before and after on different sheets or a list of the sample data and what you wish to do with it.

lucas
11-27-2006, 11:07 AM
Cal has already asked you a dozen unnecessary questions. Please provide the information required to address your question.

austenr
11-27-2006, 11:53 AM
OK. sorry. All I want to do is display something; a list box, user form, something that will give me all my totals at the end of the sub. So something like, total 1 = 123.45, total 2 = 345.67, etc. That is really all i want to do. Display the totals w/o using the msgbox which is too many to do.

Sub FindTotals()
Dim AvgLedgerBal As Double
Dim AvgCollBal As Double
Dim AvailBal As Double
Dim EarnCRCalc As Double
Dim EarnCrApplied As Double
Dim ExcessAvailBal As Double
Dim BalReq As Double
Dim BalReqOffsetPxV As Double
Dim PxVBal As Double
Dim WaivedPxV As Double
Dim EffECR As Double
Dim FinalRow As Long, i As Integer

FinalRow = Cells(65536, 1).End(xlUp).Row

For i = 2 To FinalRow
If Cells(i, 15).Value = "W" Then
WaivedPxV = WaivedPxV + Cells(i, 20).Value
Else
AvgLedgerBal = AvgLedgerBal + Cells(i, 12).Value
AvgCollBal = AvgCollBal + Cells(i, 13).Value
AvailBal = AvailBal + Cells(i, 23).Value
EarnCRCalc = EarnCRCalc + Cells(i, 19).Value
ExcessAvailBal = ExcessAvailBal + Cells(i, 28).Value
BalReq = BalReq + Cells(i, 26).Value
PxVBal = PxVBal + Cells(i, 35).Value
EarnCrApplied = Range("AJ30679").Value
EffECR = (EarnCRCalc * 12) / AvailBal
End If

Next i

End Sub

CBrine
11-27-2006, 12:03 PM
Austenr,
If you are just looking to display them, I would suggest just adding this to the end of your code.

MsgBox "Waived: " & WaivedPxV & Chr(13) & _
"AvgLedgerBal: " & avgLedgerbal & Chr(13) & _
"AvgCollBal: " & AvgCollBal & Chr(13) & _
"AvialBal: " & AvailBal & Chr(13) & _
"EarnCRCalc: " & EarnCRCalc & Chr(13) & _
"ExcessAvailBal: " & ExcessAvailBal & Chr(13) & _
"BalReq: " & BalReq & Chr(13) & _
"PxVBal: " & PxVBal & Chr(13) & _
"EarnCrApplied: " & EarnCrApplied & Chr(13) & _
"EffECR: " & EffECR

Oops, just noticed you didn't want the messagebox, but I'm assuming you were using 10. The above code only uses one to display all totals.

austenr
11-27-2006, 12:41 PM
Thanks for the solution. And sorry for not making it clearer what I wanted. It has been one of those days. The Msgbox will do fine. Solved

mdmackillop
11-27-2006, 01:02 PM
Hi Austen
You could add in some tabs, which might improve the appearance
eg

"AvgLedgerBal: " & vbtab & avgLedgerbal & Chr(13) & _

austenr
11-27-2006, 01:03 PM
Thanks Malcomb