Auto-evaluating: Auto Add after entering any value & display value in Formula Bar

Hi,

Not sure is it possible or not but still hope for a way out.

Please find the attachment .xlsm sheet -or- .Jpeg
what i trying to achieve is in the Column M we have a value, When ever user enter Value in 'M14:M1048576'. Previous value enter should not be remove instead of that should add (+) new value next to its previous value.

i.e:
when user enter 1200 in M14 it should add like this =11000+1200 and appears total value in M14 in Formula Bar when cell is selected.

It is possible when ever a user enter a value it appears with + and add the value next to the previous value

sample.jpg

sample Book1.xlsm

looking forward for a solution

Best

Hi !

Just using F2 - or double click - to edit the cell …

Hi Marc,
Thank you for your reply.
There some confusion, As the the screenshot shown, I want to use VB formulas which auto add Plus sign in front of numbers present in M14 and add calculate the total value.
To get the calculation result in column M14 without manually typing equal sign & Plus sign after each new value enter in 'M14:M1048576', is there any way to achieve it?

As editing a cell is at child level and can avoid drawback of using a VBA code …

Anyway you can do it via VBA worksheet events :
SelectionChange to save the cell formula,
Change to add the content to the previous formula …

But much ado about nothing !

No exactly sure how to do that.

KOKOSEK,

if you select the cell M27 which has a value of 9000 then you enter 1000

so the expected result is the formula =9000+1000 which is equal to 10000 but your code returns 12000 ‼

'Undo' is very not necessary neither a second column as you can see in post #6 …

Ah. I hadn't considered using the selection change to track the previous value. That's going to change more than a few things in other projects.

The second column was not necessary, but would make it easier to change the M column value if it needs to be reset.

I don't think it could be easier as more operations are necessary …

Dear all contributors,
Thank you for your kind reply and help on this. Sorry for reaching out this late as I was not able to follow the Posts due to some personal urgent work.

I have tried to use all above codes but can't make it executable.

It will be great help if you can put the code in the 'sample Book1.xlsm'' excel sheet which i was attach in P#1.


Thank you and looking forward.

Best,

Hi,
Is it possible to help me to put the code in the 'sample Book1.xlsm'' excel sheet which i was attach in P#1.
I tired it but couldn't get it through.

Thanks!

Best,

Hi,

Any help for this thread

Best,

Just copy Marc's code in post #6, open sheet1 then right click sheet1 tab > select View Code > paste the code.

Hi Akuini,
Thank you for guidance, i followed your given steps and code works appreciated.

Best,
Naveed Arif

Originally Posted by Marc L

To paste to the Sheet1 worksheet module :

PHP Code: 

Dim M$

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge = 1 And Target.Column = 13 And Target.Row > 13 Then
        If IsEmpty(Target) Then
            M = ""
        Else
            Application.EnableEvents = False
            Target.Formula = M & "+" & Target.Value2
            Application.EnableEvents = True
        End If
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge = 1 Then If Target.Column = 13 Then If Target.Row > 13 Then _
    If Target.HasFormula Then M = Target.Formula Else M = "=" & Target.Value2
End Sub 


Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

Hi Marc,

Your code works great as wanted thank you.
But there is one issue in the code, i.e when you enter any new value -or- on the previous value for example new empty cell M29 I enter value of 100 then you enter 99 again (or any other value) it work fine but you remove 99 and enter any other value instead it doesn't allow you to remove any old value. You have to remove the whole value if there is a Type and enter again from scratch.

Is it possible to make the code more flexible to add and remove any typo or previous value without entering from the very beginning.

Thank you again for amazing code

Best,

Thank you, you right but people who mostly work on these sheet are not familiar with use Excel like it is designed for. So that why I'm here trying to make the data entry easier for them.

Don't know Is it possible or not to make the code flexible fix the typos values entries without entering from the very beginning. Is there any solution of this

Thanks,

Best,

Again, the solution is to trash the code if you want to edit a cell …

The code can not be 'flexible' as Excel is not flexible, the code can't change the Excel entry behavior
or you must delete the data validation and choose a special character (which can't be any operator sign like = / * - +)
for adding the new value to the previous formula and amend the code accordingly.
For example if a cell contains 11 000 and you wand to add 1 200 the entry should be &1200 but that's so superfluous …

Or keeping the code as it is but adding a toggle button to activate / desactivate the events.

For example, if you want to edit a cell, you must first think to click this button in order to desactivate the events,
then use F2 to edit the cell then do not forget to click again the button in order to activate the events …

Or instead of a toggle button use a button to edit the cell via an UserForm but it's much ado about nothing !

Thanks, Now i know.
Appreciated your time and effort on this.

I'll make this Thread as Solved.

Thank you again

Best,