Jack

Your code will just keep adding the current entered value to the value in the
comment box, but still has no "paper trail" or a way of correcting an
incorrect entry other than entering a negative of the incorrect entry and
re-entering the correct entry.

I prefer the listing of the numbers in a separate column so's I can check all
the inputs.


Gord

On Fri, 4 Mar 2005 10:05:58 +0100, "Jack Sons" wrote:

Jab and Gord,

I tried a lot and with very much help from Tim Williams I got the code
below. Each time a new number is entered in a cell that new number is added
to the content of the comment of that cell. So no need for extra columns.
The last entry remains in the cell so it is "remembered" and the accumulated
sum of all entries in that cell is in its comment. Is that what you where
looking for Jab?

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim cmt As Comment

If Target.Cells.Count 1 Then Exit Sub

On Error Resume Next
Set cmt = Target.Comment
On Error GoTo 0

If cmt Is Nothing Then
Set cmt = Target.AddComment(Text:="0")
End If

If cmt.Text < "" Then
cmt.Text CStr(Target.Value + CDbl(cmt.Text))
End If

End Sub



"Gord Dibben" <gorddibbATshawDOTca schreef in bericht
.. .
Jab

You could use a worksheet_change event macro to enter the contents of the
input cell into column B at the next available empty row.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo stoppit
Application.EnableEvents = False
If Target.Address = "$A$2" And Target.Value < "" Then
ActiveSheet.Cells(Rows.Count, 2).End(xlUp) _
.Offset(1, 0).Value = Target.Value
End If
stoppit:
Application.EnableEvents = True
End Sub

Right-click on your sheet tab and select "View Code". Copy the above code
into the module that opens.

Using A2 as the input cell, any new number entered will be automatically
placed into Column B(starting at B2)at the next available empty row.

I would enter in C2 =Sum(B:B) or =Sum(B2:B500). Whatever you think you
need
to gather all future values in Column B.

Now you have a "paper trail" and a Totalizer cell(C2)

Note: if a mistake is made in last entered number in A2 , you will have to
delete the contents of the last cell in Column B then re-enter in A2.


Gord

On Sun, 27 Feb 2005 19:01:02 -0800, jab
wrote:

Thanks for youir help, I wantn't try that method its to risky.

I my form, I have three columns I am work with. I am making a spread
sheet
so I can keep up with how must of a items is used for week to week. I want
to
be able to enter a number in one column, say 50 that will be the fix
amount.
I then want to have another colum to enter the items used that week, and
them
another column to keep a track of how many been used for the project.
Could
you help me, Thanks

"jab" wrote:

I am using Excel 2000. I want my cell to remember the number in it and
add
an additional number each time I need to retotal.