# Listbox Calculation and Sum

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

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:

Set the number of columns to display to 6,
Set the Header to the string after the comments characters (// ), above,

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

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

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

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

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