Auto Sum entries in a cell

[Mike]

Hi, let's say I entered 5 in A1 and then the next day I updated the cell to
6. Can Excel total these two entries in A1 to get a total of 11. I don't
want to use a separate cell to add up each entry.

Your help is greatly appreciated

 

[Bernie Deitrick]

Mike,

Add this to a standard module

Option Explicit
Public myValue As Double


Add these two to the worksheet's codemodule

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Application.EnableEvents = False
Target.Value = Target.Value + myValue
myValue = Target.Value
Application.EnableEvents = True
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then myValue = Target.Value
End Sub


Add this to the workbook's code module, changing the Sheet1 to the correct sheet name:

Private Sub Workbook_Open()
myValue = Worksheets("Sheet1").Range("A1").Value
End Sub

HTH,
Bernie
MS Excel MVP

 

[Gord Dibben]

Mike

Bernie's code will do the job, but you will have no "audit trail" in the
event of incorrect data input.

This can lead to problems.


Gord Dibben MS Excel MVP