Input Cell

[Debbe]

I would like to program a cell to be a number input cell
in excel and another cell to keep a sum of all the data
that inputed. What's the best way to do that.

 

[Don Guillett]

Here is one that works for the SAME cell. Set up for A5.
Right click on sheet tab>view code>insert this>save workbook
Use 0 to start a new series
=====
Option Explicit
Dim oldvalue As Double
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$A$5" 'If Target.Value = oldvalue Then
Application.EnableEvents = False
If Target.Value = 0 Then oldvalue = 0
Target.Value = 1 * Target.Value + oldvalue
oldvalue = Target.Value
Application.EnableEvents = True
End If
End Sub
'use this if needed if it stops working
Sub Fixit()
Application.EnableEvents = True
End Sub

 

[Cody Dawg]

This is almost exactly what I've been looking for. The only difference is
that I would like to have the new value placed in another cell, let's say
B5. So you would enter a new value in A5 and it would update B5 with the
old value plus the new value. Any thoughts on how do to this? Obviously, I
new to VBA.

Also, one minor error in the code you suggested. It should be:

Option Explicit
Dim oldvalue As Double
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$A$5" Then
Application.EnableEvents = False
If Target.Value = 0 Then oldvalue = 0
Target.Value = 1 * Target.Value + oldvalue
oldvalue = Target.Value
Application.EnableEvents = True
End If
End Sub
'use this if needed if it stops working
Sub Fixit()
Application.EnableEvents = True
End Sub

 

[Don Guillett]

OK try this one
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Address(False, False) = "A1" Then
If IsNumeric(.Value) Then
Application.EnableEvents = False
Range("B1").Value = Range("B1").Value + .Value
Application.EnableEvents = True
End If
End If
End With
End Sub

 

[Cody Dawg]

Awesome!!! That was a major help - THANKS!

OK try this one
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Address(False, False) = "A1" Then
If IsNumeric(.Value) Then
Application.EnableEvents = False
Range("B1").Value = Range("B1").Value + .Value
Application.EnableEvents = True
End If
End If
End With
End Sub


Obviously,