In cells addition

[holy_eti]

Hello everyone,

I would like to know if there is anyway to do in cells addition. As an exemple, the value of the cell A2 is 2. When I put another value in cell A2 exemple 3, I would like the value of cell A2 to be 5 and not 3. So an addition of the old value and the new value. I can deal either with VBA or the Sheet itself. The persons who's gonna use this isn't a computer friendly user so he does'nt know a lot about computer. I only want him to change numbers and no formula to change.

Thank you

 

[James_Latimer]

I cant think of a way to do this without storing the existing values somewhere else (be it in a public variable or on another sheet), but that means duplicating values which may be an issue depending upon the size and complexity of your workbook. May i ask why you'd want the cell to perform like that?

 

[Joe4]

We can use some event procedure code to do this.

First, we have a Worksheet_SelectionChange event procedure that captures the value of A2 and stores it in a public variable when cell A2 is selected.
Then, we have a Worksheet_Change event procedure that will add any new value entered to the previous value (stored in our public variable).

The VBA code for all of that would look something like this:

Public StoredValue As Double


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'   Capture value of A2 when it is selected
    If (Target.Count = 1) And (Target.Address(0, 0) = "A2") Then
        If IsNumeric(Target.Value) Then StoredValue = Target.Value
    End If
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
'   Add value of A1 to previous value of A2
    If (Target.Count = 1) And (Target.Address(0, 0) = "A2") Then
        If IsNumeric(Target.Value) Then
            Application.EnableEvents = False
            Target.Value = Target.Value + StoredValue
            Application.EnableEvents = True
        End If
    End If
End Sub

In order for this to work properly, this VBA code MUST be stored in the proper sheet module. The easiest way to get there is to right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste the code above in the resulting VB Editor window. Then it will run automatically.

 

[holy_eti]

@James_Latimer . I am putting this in a manufacture where the operator must add cartboard bundle in a machine. My workbook is used to compile all these bundle with the time. So more than one batch of bundles can be added during an hour. They want the file to be as easy as it can be and they asked me to do this. So here I am...

 

[holy_eti]

@Joe4 Thank you, I will try that!

 

[James_Latimer]

Ahh I see. Lol. I’ve been between a hard place and a rock before too.
Joe4’s reply should give you what you want. Good luck mate!

 

[holy_eti]

Thank you !

 

[Joe4]

You are welcome.

 

[holy_eti]

@Joe4 While I am entering the code, I have an error: Ambiguous name on the Public_Sub Worksheet_Change(ByVal Target as Range) line. Do you have any clue on how do I fix this?

 

[Joe4]

It sounds like you already have a procedure named "Public_Sub Worksheet_Change(ByVal Target as Range)" in that module.
Is that the case? If so, what code do you have in there?
You cannot have two procedures with the same name in the same module. They would need to be combined.
If you are not sure how to do that, please post the current "Public_Sub Worksheet_Change(ByVal Target as Range)" code you have in there from before.