cummulitive totals

LeAnn,

Give this a try. Copy the following code. Open your workbook (or better yet, a copy, just in case) and then open the VB Editor (Alt + F11). In the Project Explorer window expand the tree under your workbook until you can see your payroll sheet listed (its in the folder "Microsoft Excel Objects".) Double click on the sheet listing, that will open up a blank code page. Paste the following code into that page. Then give it a try and see if this does what you want.

Public CellContents As Double
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
CellContents = 0
'Stop
CellContents = ActiveCell.Value

End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Stop
Application.EnableEvents = False
ActiveCell.Value = ActiveCell.Value + CellContents
Application.EnableEvents = True
End Sub

The "Stop" code in the above has been commented out (that's what the ' does) so they don't affect execution. You can remove them if you want; they are in there for debugging purposes (which, hopefully, you won't have to do.) Don't paste this code into a "Macro" sheet. Its an event driven code and needs to be "embedded" in the actual sheet object.

enjoy

ps: If, when you open the VB Editor, the Project Explorer isn't open then click on the View menu item and select it from the list provided.

: I have rows, by employee name, and columns by month. I would like to have the cell for row for John Smith column January, to ADD all his payroll, as I enter each paycheck. Cell B2 would then have 100.00 from the first week, and when I enter 50.00 from week 2, I do not have to overwrite it or use the calculator to find the answer. it would give me $150.00 as the total.

THANKS FOR YOUR QUICK RESPONSE.
but, to better explain what I am trying to do. I recently took over accounting for a small business. The previous person did not file copies of the W2 forms and other documents for the past 2 years. I am going through manual books, trying to get the totals by month and quarterly and yearly. (for 2001, I have a customized program, but I am unable to find all the correct files to use it for previous years) I am not too familiar with excel. but from your answer, it appears that you assumed I had payroll in excel already. I do not. I am entering the information per month, and using a calculator, I have found balancing errors and thought that if I could just enter the amount and add it, as I go... instead of alter it within the cell, it would make things easier.
thanks
LeAnn
PLEASE HELP, (I have a headache!) LeAnn,

: I have rows, by employee name, and columns by month. I would like to have the cell for row for John Smith column January, to ADD all his payroll, as I enter each paycheck. Cell B2 would then have 100.00 from the first week, and when I enter 50.00 from week 2, I do not have to overwrite it or use the calculator to find the answer. it would give me $150.00 as the total.

"bug" fix

LeAnn,

The code I posted previously has a slightly serious flaw in it (to my way of thinking, anyway). If you are like me and enter data by using the cursor arrows the previous code will not give you what you want. If you just hit the "Enter" key it works fine. The following code corrects the problem and works with either method of data entry.

Public CellContents As Double

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
CellContents = 0
CellContents = ActiveCell.Value
End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim intColumn As Double
Dim intRow As Double

For Each cell In Target
dblColumn = cell.Column
dblRow = cell.Row
Application.EnableEvents = False
Cells(dblRow, dblColumn).Value = Cells(dblRow, dblColumn).Value + CellContents
Application.EnableEvents = True
Next cell

End Sub

Hope my previous blunder didn't cause you any problems. You're just seeing the "sausage making" that is programming. LOL

take care

LeAnn,

: I have rows, by employee name, and columns by month. I would like to have the cell for row for John Smith column January, to ADD all his payroll, as I enter each paycheck. Cell B2 would then have 100.00 from the first week, and when I enter 50.00 from week 2, I do not have to overwrite it or use the calculator to find the answer. it would give me $150.00 as the total.

LeAnn,

You don't need to have your data in Excel all ready. Actually, you don't even need to have a saved file. Just paste the code as I explained before and you'll be off and running. The code that I've included with this post has a slight change to it that checks to see if the info you're inputting is numeric or not, this will allow you to enter peoples names as you come to them without having the code fail.

Public CellContents As Double

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
CellContents = 0
'Stop
CellContents = ActiveCell.Value
End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim intColumn As Integer
Dim intRow As Integer
'Stop
For Each cell In Target
intColumn = cell.Column
intRow = cell.Row
If IsNumeric(cell.Value) Then
Application.EnableEvents = False
Cells(intRow, intColumn).Value = Cells(intRow, intColumn).Value + CellContents
Application.EnableEvents = True
End If
Next cell

End Sub

Other than using this code the only methods I know of are the ones you are probably using already. My code just saves you the effort of editing the cell contents.

good luck

THANKS FOR YOUR QUICK RESPONSE. but, to better explain what I am trying to do. I recently took over accounting for a small business. The previous person did not file copies of the W2 forms and other documents for the past 2 years. I am going through manual books, trying to get the totals by month and quarterly and yearly. (for 2001, I have a customized program, but I am unable to find all the correct files to use it for previous years) I am not too familiar with excel. but from your answer, it appears that you assumed I had payroll in excel already. I do not. I am entering the information per month, and using a calculator, I have found balancing errors and thought that if I could just enter the amount and add it, as I go... instead of alter it within the cell, it would make things easier. thanks LeAnn PLEASE HELP, (I have a headache!)

Re: "bug" fix THANK YOU

Bariloche,
thank you so very very much.
it works great!
I thought it would be easier to do than that, but as long as it works, I will be grateful forever.
If it is not too much of a bother, would you send me an email with your address in case I have further questions? my email is: xmercedes@hotmail.com
thanks again
LeAnn :-)

LeAnn, The code I posted previously has a slightly serious flaw in it (to my way of thinking, anyway). If you are like me and enter data by using the cursor arrows the previous code will not give you what you want. If you just hit the "Enter" key it works fine. The following code corrects the problem and works with either method of data entry. Public CellContents As Double Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim intColumn As Double Dim intRow As Double For Each cell In Target dblColumn = cell.Column dblRow = cell.Row Application.EnableEvents = False Cells(dblRow, dblColumn).Value = Cells(dblRow, dblColumn).Value + CellContents Application.EnableEvents = True Next cell Hope my previous blunder didn't cause you any problems. You're just seeing the "sausage making" that is programming. LOL take care

: LeAnn, : Give this a try. Copy the following code. Open your workbook (or better yet, a copy, just in case) and then open the VB Editor (Alt + F11). In the Project Explorer window expand the tree under your workbook until you can see your payroll sheet listed (its in the folder "Microsoft Excel Objects".) Double click on the sheet listing, that will open up a blank code page. Paste the following code into that page. Then give it a try and see if this does what you want. : : Public CellContents As Double : Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) : CellContents = 0 : 'Stop : CellContents = ActiveCell.Value : : End Sub : Private Sub Worksheet_Change(ByVal Target As Excel.Range) : 'Stop : Application.EnableEvents = False : ActiveCell.Value = ActiveCell.Value + CellContents : Application.EnableEvents = True : End Sub : : The "Stop" code in the above has been commented out (that's what the ' does) so they don't affect execution. You can remove them if you want; they are in there for debugging purposes (which, hopefully, you won't have to do.) Don't paste this code into a "Macro" sheet. Its an event driven code and needs to be "embedded" in the actual sheet object. : : enjoy : : ps: If, when you open the VB Editor, the Project Explorer isn't open then click on the View menu item and select it from the list provided. :

thanks,
I did not try it with this latest fix, but I did find that problem yesterday as I was experimenting with it.(it would not allow me to enter alpha) Also, when I tried to save and exit, a line came up yellow suggesting to "fix the bug". I hope that this will cover the problem, if not I will let you know. Your help is very much appreciated.
thanks, LeAnn :-} LeAnn, You don't need to have your data in Excel all ready. Actually, you don't even need to have a saved file. Just paste the code as I explained before and you'll be off and running. The code that I've included with this post has a slight change to it that checks to see if the info you're inputting is numeric or not, this will allow you to enter peoples names as you come to them without having the code fail. Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim intColumn As Integer Dim intRow As Integer For Each cell In Target intColumn = cell.Column intRow = cell.Row If IsNumeric(cell.Value) Then Application.EnableEvents = False Cells(intRow, intColumn).Value = Cells(intRow, intColumn).Value + CellContents Application.EnableEvents = True End If Next cell Other than using this code the only methods I know of are the ones you are probably using already. My code just saves you the effort of editing the cell contents. good luck

THANK YOU SO MUCH FOR YOUR HELP
THE LATEST FIX IS A GOOD ONE. it was needed to title the columns and rows.
I do have a slight problem, I cannot use the "SUM KEY" (the one that looks like a sideway W)to do quick totals for balancing....BUT, you cut my time about in half, so I am very greatful for that. and a little bit of adding now won't be so difficult.
I am almost done with everything.. so no need to change the program again. I most likely won't need it.
thanks again
LeAnn LeAnn, You don't need to have your data in Excel all ready. Actually, you don't even need to have a saved file. Just paste the code as I explained before and you'll be off and running. The code that I've included with this post has a slight change to it that checks to see if the info you're inputting is numeric or not, this will allow you to enter peoples names as you come to them without having the code fail. Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim intColumn As Integer Dim intRow As Integer For Each cell In Target intColumn = cell.Column intRow = cell.Row If IsNumeric(cell.Value) Then Application.EnableEvents = False Cells(intRow, intColumn).Value = Cells(intRow, intColumn).Value + CellContents Application.EnableEvents = True End If Next cell Other than using this code the only methods I know of are the ones you are probably using already. My code just saves you the effort of editing the cell contents. good luck

LeAnn,

You're welcome. For quick totals you can highlight the cells you want to sum and the sum will be shown at the bottom right part of the Excel window (right click on that area and you can select from different operations, such as average, max, min, etc). Alternatively, if you don't need the program anymore, you can either delete it or comment it out by putting a single quote (') in front of each code line (like I did with the Stop command).

take care

THANK YOU SO MUCH FOR YOUR HELP THE LATEST FIX IS A GOOD ONE. it was needed to title the columns and rows. I do have a slight problem, I cannot use the "SUM KEY" (the one that looks like a sideway W)to do quick totals for balancing....BUT, you cut my time about in half, so I am very greatful for that. and a little bit of adding now won't be so difficult. I am almost done with everything.. so no need to change the program again. I most likely won't need it. thanks again LeAnn