Hi to All.
I’m having trouble with the listbox on calculating the sum values inside a rows. Just Like Autosum on excel
can anyone help me?
Thanks
Hello Jude,
What’s the error you are getting? What is your code?
Julen
In the quantity LostFocus column active the computations ?
i Still don’t know what code to use. i’m just a beginner on coding and only self studying. and i want to learn mainly on listbox and sql.
If you do not try to write any code you will never learn.
If you do not explain in detail(s) - not too much details - how can we help ?
Try this:
[code]
Dim Total As Double
// Part # Designation Color Quantity U.P. Total
Me.AddRow “123A456”,“Plymouth Savage”,“Light Blue”,“1”,“10,456”,“Total”
Me.AddRow “123B456”,“Plymouth Jungle”,“Gold”,“2”,“17,456”,“Total”
Total = CDbl(Me.Cell(0,3)) * CDbl(Me.Cell(0,4))
Me.Cell(0,5) = Format(Total,"###.###.###")
Total = CDbl(Me.Cell(1,3)) * CDbl(Me.Cell(1,4))
Me.Cell(1,5) = Format(Total,"###.###.###")[/code]
Explanation:
Add a Listbox in WIndow1,
Set the number of columns to display to 6,
Set the Header to the string after the comments characters (// ), above,
Add an Open Event Handler,
Copy the code above and paste it in the Open handler,
Run.
Note: I do not deal with the cents (no “.00” at the end of the Total column).
Ask the documentation if you need to know what Format, CDbl, Me are, how to use it, and why 6 columns, but the Total column is column 5, same for 3 and 4.
dim sum as double
dim Column as Integer = 0
for row as integer = 0 to ListBox1.ListCount-1
sum = sum + val(cell(row, column))
next
http://documentation.xojo.com/index.php/ListBox.Cell
http://documentation.xojo.com/index.php/Listbox
Hey Jude ?
Where are you ?
Is this helped you ?
sorry internet is unstable
This helps a lot. this is much easier than doing a loop like IF
Thanks everybody
The key points here:
- You need to add one by one the values in the cells of a row. To address a cell you need the Listbox.Cell method, which requires you to specify the row and column number of the cell you want to read, and it returns the TEXT in that cell (not a numeric value).
- To calculate the sum you need to convert each text value to their numeric equivalent (check the Val function)
- Either you have a fixed size listbox and then you can know beforehand how many rows and cells per row you have, or you need to use listbox.listcount and listbox.columncount to figure out how many elements to add.
- All this needs to happen at a defined moment= when an event is triggered (for example, there is a change in one of the cells). Check the events exposed by the ListBox class and see which one fits your design. I would say CellLostFocus or CellTextChange could be good candidates. That’s where the code needs to be placed.
If you have any question don’t hesitate to ask.
Julen
More information:
To Edit a Cell, Add a CellClick Event annd put the code below:
Function CellClick(row as Integer, column as Integer, x as Integer, y as Integer) As Boolean
// From the Language Reference (Listbox)
Me.CellType(row,column) = ListBox.TypeEditable
Me.EditCell(row,column)
End Function
Then, you will be able to modify the data (from my previously shared code).
To execute a value change (in Quantity or/and Price Unit columns), use the CellLostFocus Event, and compute the new value.
Of course in a real application, the logic is far complex. But to test an idea / learn, this is good.
Where do you place this code ?
Why executing at each loop iteration the dim as Integer ? not very effective slow down the global applciation performance.
It Works. Just Need to edit a little.
I Put The Code in a button and open a new window so that the total will be in it.
This code works great for me
dim sum as double
dim Column as Integer = 0
for row as integer = 0 to ListBox1.ListCount-1
sum = sum + val(cell(row, column))
next
as i needed to add rows if necessary and sums all the rows that are added or removed
Thank You Everyone for the help.I Added another knowledge again.
You can go a step further by putting the code in a place where it will execute automatically, if that’s better. That “place” is an event handler. I wrote about it in my previous post.
Let us know if you need help with that.
Julen
Last idea: use XojoScript to do the Calculation and Sum jobs.
@Emile Schwarz
May I ask you two questions please. Both in reference to your answer above.
Why doesn’t below work? I get the dollar amount in the first column but I get $0.00 in the total column…
Dim Total As Double
// Part # Designation Color Quantity U.P. Total
Me.AddRow “123A456”,“Plymouth Savage”,“Light Blue”,“1”,Format(CDbl(“10,456”),"\$###,##0.00"),“Total”
Me.AddRow “123B456”,“Plymouth Jungle”,“Gold”,“2”,Format(CDbl(“17,456”),"\$###,##0.00"),“Total”
Total = CDbl(Me.Cell(0,3)) * CDbl(Me.Cell(0,4))
Me.Cell(0,5) = Format(Total,"\$###,##0.00")
Total = CDbl(Me.Cell(1,3)) * CDbl(Me.Cell(1,4))
Last question… How would you total the columns… For example add the last columns together and display a total at the bottom?
i use sub rutine to call all col and row at one time after refresh or call the sub rutine
[quote=410294:@Lance Mehle]@Emile Schwarz
May I ask you two questions please. Both in reference to your answer above.
Why doesn’t below work? I get the dollar amount in the first column but I get $0.00 in the total column…
Dim Total As Double
// Part # Designation Color Quantity U.P. Total
Me.AddRow “123A456”,“Plymouth Savage”,“Light Blue”,“1”,Format(CDbl(“10,456”),"\$###,##0.00"),“Total”
Me.AddRow “123B456”,“Plymouth Jungle”,“Gold”,“2”,Format(CDbl(“17,456”),"\$###,##0.00"),“Total”
Total = CDbl(Me.Cell(0,3)) * CDbl(Me.Cell(0,4))
Me.Cell(0,5) = Format(Total,"\$###,##0.00")
Total = CDbl(Me.Cell(1,3)) * CDbl(Me.Cell(1,4))
Last question… How would you total the columns… For example add the last columns together and display a total at the bottom?[/quote]
Because the cells being referenced are strings!
You’re pulling a value back from a string that is formatted with a “$” dollar sign. That kills the number extraction and returns zero. Strip the dollar signs off of Cell(0,4) and Cell(1,4) before you pass them through CDbl.
Total = CDbl(Me.Cell(0,3)) * CDbl(ReplaceAll(Me.Cell(0,4), “$”, “”))