Thread: Add value to cell...

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...

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.

Hi C,

See J.E. McGimpsey at:

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


---
Regards,
Norman


"ctroyp" <ctroyp.29agvn_1150115104.2069@excelforum-nospam.com> wrote in
message news:ctroyp.29agvn_1150115104.2069@excelforum-nospam.com...
>
> 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
>

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
dguillett1@austin.rr.com
"ctroyp" <ctroyp.29agvn_1150115104.2069@excelforum-nospam.com> wrote in
message news:ctroyp.29agvn_1150115104.2069@excelforum-nospam.com...
>
> 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
>

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:

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

--
---
Regards,
Norman



"ctroyp" <ctroyp.29anld_1150123808.5052@excelforum-nospam.com> wrote in
message news:ctroyp.29anld_1150123808.5052@excelforum-nospam.com...
>
> 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
>

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" <ctroyp.29anld_1150123808.5052@excelforum-nospam.com> wrote in
message news:ctroyp.29anld_1150123808.5052@excelforum-nospam.com...
>
> 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
>

Originally Posted by Norman Jones

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 myself with the object properties. This is exactly what I used if anyone needs to know. Thanks Norman and Don.

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

We're glad to help

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"ctroyp" <ctroyp.29apg4_1150126214.9666@excelforum-nospam.com> wrote in
message news:ctroyp.29apg4_1150126214.9666@excelforum-nospam.com...
>
> 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]

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.