Solved

Datagridview with totals for rows and columns

Posted on 2007-04-07
7
2,550 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month5 days, 9 hours left to enroll

626 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