Solved

Datagridview with totals for rows and columns

Posted on 2007-04-07
7
2,542 Views
Last Modified: 2013-12-25
Hi Experts - Is it possible to have an 1 unbound datagridview (containing a fixed number of rows) that receives figures from the user and has BOTH row totals  (last column of datagridview) and column totals (last row of datagridview). Both  totals would need to update on an event. I have been messing around with the cell_value_changed and cell_leave events but i fear that this will cause a stack overflow. Can anyone tell me if it is possible or whether i need to have one of the totals in a seperate grid or textboxes.
Or should i be using some sort of spreadsheet control that accepts formulas?
0
Comment
Question by:staritsj
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 18

Expert Comment

by:Priest04
ID: 18870475
If user is entereing data manually, then you can safely use CellValueChanged event for calculating total values. Just mark the last row and last col as read only and in CellValueChanged event put the code for calculating totals. Basically, you only calculate the total for the current row, current column, and the last value which is the total of all rows (cols) totals.

Goran
0
 

Author Comment

by:staritsj
ID: 18870771
Thanks Goran,

Does this still apply if i have several sub total colums within the grid. For example lets say there are 10 data columns in the grid. After the first five there is a column totalling the previous 5 and after the next 5 there is a another sub-total column followed by a grand total column. Will i still be able to use a datagridview and the CellValueChanged event?
0
 

Author Comment

by:staritsj
ID: 18871072
Hi Goran, I must be doing something wrong. I set up a little test datagridview with 4 columns and 3 rows. I placed the following on cell_value_changed event for the datagridview dgvTest.
***********************************************************************************************'
Private Sub dgvTest_CellValueChanged(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvTest.CellValueChanged
        If Me.dgvTest.RowCount > 0 Then
            If e.RowIndex <> -1 Then
'GET ROW TOTAL
                Me.dgvTest.Rows(Me.dgvTest.RowCount - 1).Cells(e.ColumnIndex).Value = CInt(Me.dgvTest.Rows(0).Cells(e.ColumnIndex).Value) + CInt(Me.dgvTest.Rows(1).Cells(e.ColumnIndex).Value)
'GET COLUMN TOTAL
                Me.dgvTest.Rows(e.RowIndex).Cells(3).Value = CInt(Me.dgvTest.Rows(e.RowIndex).Cells(0).Value) + CInt(Me.dgvTest.Rows(e.RowIndex).Cells(1).Value) + CInt(Me.dgvTest.Rows(e.RowIndex).Cells(2).Value)
                End If
        End If
    End Sub
****************************************************************************************************************
The problem appears to be that the 'e' reference is changing as the values in the TOTAL column and TOTAL row are changed. I don't know how else to reference the column and row and change only those totals as you suggested. The problem is that the on_cell_value_changed event keeps firing. Is there anyway of disabling this event after the initial firing.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 18

Expert Comment

by:Priest04
ID: 18871954
Yes, CellValueChanged event is fired everytime you set the total value for each cell. So in order to use CellValueChanged event, you would then need to check for the e.ColumnIndex and see if it is the index of a total row(col). Another way, better i believe, would be to use CellBeginEdit and CellEndEdit events

In CellBeginEdit, you should store the current value of the cell in a module variable. This would be the "old" value. Below is the code for catching the new value, and it would be the same for catching the oldvalue, only the OldValue variable should be declared on module level.

In CellEndEdit, which wold be fired only once (not for all total cols/rows) the code would simply look like this:

dim NewValue as double

; get the new value
Double.TryParse(dg.Item(e.columnindex,e.rowindex).value.tostring,newvalue)

' and now for every total column/row you should do calculations, I will give example for 4th column only
dg.item(4,e.rowindex).value=cdbl(dg.item(4,e.rowindex).value)+NewValue-OldValue

Goran
0
 
LVL 18

Expert Comment

by:Priest04
ID: 18871957
One thing more, you could also parse dg.item(4,e.rowindex).value to double, if you are not certain it will always hold the value

dim NewValue as double
Double.TryParse(dg.Item(e.columnindex,e.rowindex).value.tostring,newvalue)

' and now for every total column/row you should do calculations, I will give example for 4th column only
dim TotalValue as double

Double.TryParse(dg.Item(4,e.rowindex).value.tostring,TotalValue)
TotalValue+=NewValue-OldValue
dg.Item(4,e.rowindex).value=TotalValue

Goran
0
 
LVL 18

Assisted Solution

by:Priest04
Priest04 earned 200 total points
ID: 18871967
I forgot to add, afteryou have loaded data in datagridview, you need to do all total calulcations, since the above example is only used to calculate changed values.

Goran
0
 
LVL 34

Accepted Solution

by:
Sancler earned 300 total points
ID: 18872409
Can I suggest you have a look at the DataGridView's CellValidating and CellValidated events?  Their use might have two advantages.  First, they only fire when the USER changes a value in a cell, not when it is changed programmatically: so that should get rid of unwanted repeat-firing when you are changing the totals.  Secondly, if you use the CellValidating event, you can check for correct user entry (and if it is incorrect, send the user back to the cell concerned) before moving into your total-calculating routine.

Roger
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question