Running Total - Bariloche

Teri,

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."
ActiveCell.Select
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

Bariloche,
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 this...in 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.

NAME OT HRS. TOTAL
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

OT HRS. TOTAL
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.

Teri

Teri,

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."
        ActiveCell.Select
    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
                Target.Select
            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.