Solved

Datagridview with totals for rows and columns

Posted on 2007-04-07
7
2,527 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
  • 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

743 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now