Add value to cell...
 

Hello, I was wondering if someone could help me to add the code to d
the following in a worksheet:
Assume that the sheet has current data. The user clicks on a cell (fo
example that contained the number 500) and enters the number 5 and hit
enter or tabs to another field. I would like the number 5 that wa
entered to be added to 500. Again, this would be updated by eithe
hitting enter or moving to another field.

I have attempted this, but could not get it to work. Below is my cod
if it helps anyone to understand what i am trying to do. I woul
appreciate any help...


Code
-------------------
Dim intExistVal As Integer
Dim intCellCol As Integer
Dim intCellRow As Integer
Dim temp

Private Sub Worksheet_Change(ByVal Target As Range)
Sheet1.Range(intCellCol + intCellRow).Value = Sheet1.Range(intCellCol + intCellRow).Value + intExistVal
intExistVal = Empty
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
intExistVal = ActiveCell.Value
intCellCol = ActiveCell.Column
intCellRow = ActiveCell.Row
End Su
-------------------


also, I tried using Cell(intCellCol + intCellRow) and could not get th
Cell object members to pull down after entering a following period. No
sure why this doesn't work

--
ctroy
-----------------------------------------------------------------------
ctroyp's Profile: http://www.excelforum.com/member.php...fo&userid=3532
View this thread: http://www.excelforum.com/showthread.php?threadid=55097

Add value to cell...
 

Hi C,

See J.E. McGimpsey at:

http://www.mcgimpsey.com/excel/accumulator.html


---
Regards,
Norman


"ctroyp" wrote in
message ...

Hello, I was wondering if someone could help me to add the code to do
the following in a worksheet:
Assume that the sheet has current data. The user clicks on a cell (for
example that contained the number 500) and enters the number 5 and hits
enter or tabs to another field. I would like the number 5 that was
entered to be added to 500. Again, this would be updated by either
hitting enter or moving to another field.

I have attempted this, but could not get it to work. Below is my code
if it helps anyone to understand what i am trying to do. I would
appreciate any help...


Code:
--------------------
Dim intExistVal As Integer
Dim intCellCol As Integer
Dim intCellRow As Integer
Dim temp

Private Sub Worksheet_Change(ByVal Target As Range)
Sheet1.Range(intCellCol + intCellRow).Value = Sheet1.Range(intCellCol +
intCellRow).Value + intExistVal
intExistVal = Empty
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
intExistVal = ActiveCell.Value
intCellCol = ActiveCell.Column
intCellRow = ActiveCell.Row
End Sub
--------------------


also, I tried using Cell(intCellCol + intCellRow) and could not get the
Cell object members to pull down after entering a following period. Not
sure why this doesn't work.


--
ctroyp
------------------------------------------------------------------------
ctroyp's Profile:
http://www.excelforum.com/member.php...o&userid=35321
View this thread: http://www.excelforum.com/showthread...hreadid=550972

Add value to cell...
 

start with 0 then whatever you put in will be added to the last value

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$A$5" Then
On Error GoTo fixit
Application.EnableEvents = False
If Target.Value = 0 Then oldvalue = 0
Target.Value = 1 * Target.Value + oldvalue
oldvalue = Target.Value
fixit:
Application.EnableEvents = True
End If
End Sub

--
Don Guillett
SalesAid Software

"ctroyp" wrote in
message ...

Hello, I was wondering if someone could help me to add the code to do
the following in a worksheet:
Assume that the sheet has current data. The user clicks on a cell (for
example that contained the number 500) and enters the number 5 and hits
enter or tabs to another field. I would like the number 5 that was
entered to be added to 500. Again, this would be updated by either
hitting enter or moving to another field.

I have attempted this, but could not get it to work. Below is my code
if it helps anyone to understand what i am trying to do. I would
appreciate any help...


Code:
--------------------
Dim intExistVal As Integer
Dim intCellCol As Integer
Dim intCellRow As Integer
Dim temp

Private Sub Worksheet_Change(ByVal Target As Range)
Sheet1.Range(intCellCol + intCellRow).Value = Sheet1.Range(intCellCol +
intCellRow).Value + intExistVal
intExistVal = Empty
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
intExistVal = ActiveCell.Value
intCellCol = ActiveCell.Column
intCellRow = ActiveCell.Row
End Sub
--------------------


also, I tried using Cell(intCellCol + intCellRow) and could not get the
Cell object members to pull down after entering a following period. Not
sure why this doesn't work.


--
ctroyp
------------------------------------------------------------------------
ctroyp's Profile:
http://www.excelforum.com/member.php...o&userid=35321
View this thread: http://www.excelforum.com/showthread...hreadid=550972

Add value to cell...
 

Ok, I figured it out. Using the link provided I was able to ge
started, but I had to use a public variable in order to run th
function across all cells on the sheet.

I am having one issue though. When selecting multiple cells, I get
type-mismatch error when setting my accumulator value. Obviously
don't want to select multiple cells to enter a value, but how can
test for a multiple cell selection in the Worksheet_SelectionChang
event before setting the dblAcc value?

Here is the code:

Code
-------------------
Public dblAcc As Double

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If Not IsEmpty(.Value) And IsNumeric(.Value) Then
dblAcc = dblAcc + .Value
Else
dblAcc = 0
End If

Application.EnableEvents = False
.Value = dblAcc
Application.EnableEvents = True
End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
dblAcc = Target.Value
End Su
-------------------

--
ctroy
-----------------------------------------------------------------------
ctroyp's Profile: http://www.excelforum.com/member.php...fo&userid=3532
View this thread: http://www.excelforum.com/showthread.php?threadid=55097

Add value to cell...
 

--
---
Regards,
Norman



"ctroyp" wrote in
message ...

Ok, I figured it out. Using the link provided I was able to get
started, but I had to use a public variable in order to run the
function across all cells on the sheet.

I am having one issue though. When selecting multiple cells, I get a
type-mismatch error when setting my accumulator value. Obviously I
don't want to select multiple cells to enter a value, but how can I
test for a multiple cell selection in the Worksheet_SelectionChange
event before setting the dblAcc value?

Here is the code:

Code:
--------------------
Public dblAcc As Double

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If Not IsEmpty(.Value) And IsNumeric(.Value) Then
dblAcc = dblAcc + .Value
Else
dblAcc = 0
End If

Application.EnableEvents = False
.Value = dblAcc
Application.EnableEvents = True
End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
dblAcc = Target.Value
End Sub
--------------------


--
ctroyp
------------------------------------------------------------------------
ctroyp's Profile:
http://www.excelforum.com/member.php...o&userid=35321
View this thread: http://www.excelforum.com/showthread...hreadid=550972

Add value to cell...
 

Hi

Obviously I don't want to select multiple cells to enter a
value, but how can I test for a multiple cell selection in the
Worksheet_SelectionChange event before setting the dblAcc
value?

I assume that you mean the Worksheet_Change event.

Try:

If Target.Count 1 Then Exit Sub


---
Regards,
Norman



"ctroyp" wrote in
message ...

Ok, I figured it out. Using the link provided I was able to get
started, but I had to use a public variable in order to run the
function across all cells on the sheet.

I am having one issue though. When selecting multiple cells, I get a
type-mismatch error when setting my accumulator value. Obviously I
don't want to select multiple cells to enter a value, but how can I
test for a multiple cell selection in the Worksheet_SelectionChange
event before setting the dblAcc value?

Here is the code:

Code:
--------------------
Public dblAcc As Double

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If Not IsEmpty(.Value) And IsNumeric(.Value) Then
dblAcc = dblAcc + .Value
Else
dblAcc = 0
End If

Application.EnableEvents = False
.Value = dblAcc
Application.EnableEvents = True
End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
dblAcc = Target.Value
End Sub
--------------------


--
ctroyp
------------------------------------------------------------------------
ctroyp's Profile:
http://www.excelforum.com/member.php...o&userid=35321
View this thread: http://www.excelforum.com/showthread...hreadid=550972

Add value to cell...
 

Norman Jones Wrote:
Hi

Obviously I don't want to select multiple cells to enter a
value, but how can I test for a multiple cell selection in the
Worksheet_SelectionChange event before setting the dblAcc
value?

I assume that you mean the Worksheet_Change event.

Try:

If Target.Count 1 Then Exit Sub


---
Regards,
Norman



[/color]
Ok, looks like that did it. I am still trying to familiarize mysel
with the object properties. This is exactly what I used if anyon
needs to know. Thanks Norman and Don.

Code
-------------------
Public dblAcc As Double

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If Not IsEmpty(.Value) And IsNumeric(.Value) Then
dblAcc = dblAcc + .Value
Else
dblAcc = 0
End If

Application.EnableEvents = False
.Value = dblAcc
Application.EnableEvents = True
End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count = 1 Then
dblAcc = Target.Value
End If
End Su
-------------------

--
ctroy
-----------------------------------------------------------------------
ctroyp's Profile: http://www.excelforum.com/member.php...fo&userid=3532
View this thread: http://www.excelforum.com/showthread.php?threadid=55097

Add value to cell...
 

We're glad to help

--
Don Guillett
SalesAid Software

"ctroyp" wrote in
message ...

Norman Jones Wrote:
Hi

Obviously I don't want to select multiple cells to enter a
value, but how can I test for a multiple cell selection in the
Worksheet_SelectionChange event before setting the dblAcc
value?

I assume that you mean the Worksheet_Change event.

Try:

If Target.Count 1 Then Exit Sub


---
Regards,
Norman




Ok, looks like that did it. I am still trying to familiarize myself
with the object properties. This is exactly what I used if anyone
needs to know. Thanks Norman and Don.

Code:
--------------------
Public dblAcc As Double

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If Not IsEmpty(.Value) And IsNumeric(.Value) Then
dblAcc = dblAcc + .Value
Else
dblAcc = 0
End If

Application.EnableEvents = False
.Value = dblAcc
Application.EnableEvents = True
End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count = 1 Then
dblAcc = Target.Value
End If
End Sub
--------------------


--
ctroyp
------------------------------------------------------------------------
ctroyp's Profile:
http://www.excelforum.com/member.php...o&userid=35321
View this thread: http://www.excelforum.com/showthread...hreadid=550972
[/color]

Add value to cell...
 

One last thing...sorry.

Is there a way that I can make this code available on our network share
so that anyone can import it to a particular spreadsheet?

Thanks again.


--
ctroyp
------------------------------------------------------------------------
ctroyp's Profile: http://www.excelforum.com/member.php...o&userid=35321
View this thread: http://www.excelforum.com/showthread...hreadid=550972