Input Cell

  • Thread starter Thread starter Debbe
  • Start date Start date
D

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.
 
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
 
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
 
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
 
Awesome!!! That was a major help - THANKS!

Don Guillett said:
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,
Click to expand...
 
Back
Top