| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Cell
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Input Cell
Here is one that works for the SAME cell. Set up for A5.
Right click on sheet tabview codeinsert thissave 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 "Debbe" wrote in message ... 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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Input Cell
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" wrote in message ... Here is one that works for the SAME cell. Set up for A5. Right click on sheet tabview codeinsert thissave 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 "Debbe" wrote in message ... 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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Input Cell
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" wrote in message ... 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" wrote in message ... Here is one that works for the SAME cell. Set up for A5. Right click on sheet tabview codeinsert thissave 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 "Debbe" wrote in message ... 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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Input Cell
Awesome!!! That was a major help - THANKS!
"Don Guillett" wrote in message ... 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" wrote in message ... 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" wrote in message ... Here is one that works for the SAME cell. Set up for A5. Right click on sheet tabview codeinsert thissave 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 "Debbe" wrote in message ... 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Input cell should display a percentage of input value in the cell | Excel Discussion (Misc queries) | |||
| How to input pictures automatically based on cell input? | Excel Worksheet Functions | |||
| I want excel to input data in a cell from multiple cell choices | Excel Worksheet Functions | |||
| run macro with input msg based on cell input | Excel Discussion (Misc queries) | |||
| Have user input converted to uppercase in same cell as input? | New Users to Excel | |||
Linear Mode
