MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Running Total - Bariloche

Posted by Teri on January 08, 2002 1:37 PM

Thanks, your code worked (kind of) I don't need all cell to add to the right just one, is there a way to specify which column should do this? I had to block out the first sub, because I need to be able to select more than on cell at a time. I just need Total OT Hrs. and Current OT Hrs. to add, and when I open the file the next day, I want to be able to enter new OT hours on top of the old one, but keep the Total OT Hrs. as a running total. The code you gave me kind of does it but it does it for too many columns. I know I'm not explaining this right. If cell b5 has 170 hours in it and I enter 8 more hours in a5 I want b5 to add and to read 178, then on the next day I want to enter say 4 hours in a5 and then b5 should read 183, but whenever I enter any addition hours after the first initial hours the formula reverts back to 170. I hope you understand. Just a running total in one column. Thank you so much.


Posted by Bariloche on January 08, 2002 7:06 PM


I'm confused. What you say the code doesn't do is exactly what it does do.

The first subroutine is there to prevent someone from selecting cells B5 and C5 and entering data. I did notice however, when modifiying this code for the "log changes in the comment box" thread, that that first sub is wrong. Here's the corrected code for it:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

If Selection.Columns.Count > 1 Then
MsgBox "Please select cells in one column only."
End If

End Sub

As it was, it wouldn't let you select more than one cell, regardless of whether it was in a row or column. Maybe that's what prompted you to delete it. Try the new one and see if that helps.

As far as the other subroutine goes, if you only select A5 and enter a number, that number will be entered in A5 and added to B5. If you enter another number in A5 it will replace the existing number in A5 and also be added to B5.

The reverting back to "170" behaviour that you describe is difficult for me to understand. In your example, and using my code, the 170 would no longer be stored any where. So I'm not sure how to fix that :-((

Maybe what was happening is you were selecting A5 and B5 and because the other subroutine had been deleted the main subroutine was adding your number in A5, adding it to B5 and adding it to C5. It would do that if you select cells in more than one column -- that's why the first (now fixed ;-)) ) subroutine was needed. And maybe to recover from this you hit the undo. That's the only place that I know of that would have the "170".

Give it another try and post back. Hopefully you'll be pleased by the results. But post back either way, 'cuz I don't want you to go away unhappy.

take care

Posted by Teri on January 09, 2002 10:19 AM

Posted by Teri on January 09, 2002 10:47 AM

I wish I could let you see this spreadsheet. The code doesn't add the number I enter. It sometimes adds more or doubles the existing total. I don't understand. Here's what I need, Example: I entered 8 hours, the total was 170, it should have added the 8 hours to 170 and given me a new total of 178, but it doesn't. It looks like the Total column there is a formula that adds the OT and Hrs column together. I want to be able to enter new OT hours over the old entry, but keep the total column running. Look at the first line here, the total is 176, it is the result of a formula which adds the 6 and 170 together, well I want to be able to next time enter over top of the 6, another number like 4, now the total should be 180, with the 4 adding to the 176, but when I enter 4 over 6, I get 174. I want to add to the 176 and get 180.

CUNNINGHAM,R 6 170 176
MARONI,G 4 194 198
ADAMS,G 3 221 224
HEMSHRODT,B 11 220 231
AUSTIN,E 2 229 231

Now, the code does this whenever I enter a number in OT column, it doesn't add that to Hrs. See example below 4 + 229 should read 233 not 239

VERMEULEN,G 4 229 239
LAIBACH,R 13 239 252
CHILDERS,M 9 244 253
BISHOP,M 14 242 256
BARKER,R 12 248 260
WEAVER,C 6 287 293
DENEEN,R 5 290 295
PRENTICE,D 4 291 295
SLANCHIK,F 7 292 299

I truely appreciate your help, I'm new to excel and VB, I recently returned to the work after 22 years when my husband died, so I'm teaching myself the computer (Excel in particular). And I know I'm not explaining this very well. But I still THANK you for trying. Your an angel.


Posted by Bariloche on January 10, 2002 10:19 PM


Sorry for the confusion. Give this code a try. I think we've got it now. Your explanation helped alot.

Public PreviousValue As Double

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

If Target.Column = 2 Then PreviousValue = ActiveCell.Value

If Target.Column = 2 And Target.Rows.Count > 1 Then
MsgBox "Please enter data for each row individually."
End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim intColumn As Integer
Dim intRow As Integer

intColumn = Target.Column
intRow = Target.Row
If intColumn = 2 Then
If IsNumeric(Target.Value) Then
Application.EnableEvents = False
Cells(intRow, intColumn + 1).Value = Cells(intRow, intColumn + 1).Value + PreviousValue
Application.EnableEvents = True
End If
End If

End Sub

The only thing I haven't been able to figure out is how to make this work for multiple row entry (i.e, selecting B2:Bwhatever and entering "5" in for everybody. As it is right now you'll have to enter each row individually. Not a terrible hardship, but slightly annoying.

Incidentally, this code assumes that the "OT" column is "B" (or column 2). If that is not correct, then change the twos (2) in the code to the appropriate column number.

As always, if this is correct just yell and I'll fix it.

have fun

PS: I'm trying out an HTML tag that I noticed Russell Hauf using. The code should copy correctly but there might be a little extraneous formatting that you have to delete. Just look the code over before you run it. Good luck.