Selfupdating cells autosum

[besis]

I would like to know how to create self updating cells by range or for entire sheet. By selfupdating I mean that for example if I enter in cell value 4 and later I enter value 3 in that same cell, I would like the same cell to selfupdate itself values 4+3=7.

[cytop]

Can ony be done using VBA.

For 1 cell, example A1

Please Login or Register  to view this content.

To extend this to a range you could use

Please Login or Register  to view this content.

as the check but for simplicity this is limited to single cell changes. It ignores changes to a range of cells using Ctrl-Enter

[Kevin#]

This post has been deleted

[besis]

Thank you so mutch. You have saved me a lot of time, cause I have tried so long to get my VBA code to make this work. I am new here, how can I ask VBA questions for example directly to you? Do you solve also complicated VBA problems (with compensation of course). Again thank you, and wish you only the best in the future.

[cytop]

can I ask VBA questions for example directly to you

No - all questions on the public board and whoever answers, answers.

Do you solve also complicated VBA problems (with compensation of course)

On a professional basis. I'd be happy to spend the summer in Helsinki with weekends in St. Petersburg but knowing my luck, you're probably in Rovaniemi!

I would add that what you are doing is not really a good idea (in my opinion). There is no audit of changes (but could be added) and there is no Undo as VBA clears the undo buffer, but glad it helped.

[Kevin#]

@cytop - that is a neat solution.

Here is a small mod that creates a formula in the cell so that you can see every value that has been entered in a cell
(helps spot an error if wrong value is input)

Please Login or Register  to view this content.

[cytop]

Another good alternative... and to be honest, probably preferable to mine for a simple application as the individual changes are not lost.

Mine came from a project where they wanted that kind of single cell, accumulating input but with a complete audit of who/when. That added a row to an Audit sheet recording the date, time, user, old value & new value.

I would suggest to the OP that some kind of user accessible 'Undo' is needed for those times a user mistypes - code that changes a changed cell makes it impossible for an ordinary user to correct an error.

[besis]

You are a PRO. I tried it and it works very good. Can I ask how long you have used Excel with VBA?
PS. I live about 12 km from Helsinki, as a family man I hang a lot of time with my 3 years old daughter and wife.
If you visit Helsinki this summer june 2016 I could use my time to show you Helsinki :-)

(Thank you)²

[besis]

One thing I noticed in the option when formulas are created in cells is that if users uses DELETE key it brings up the debuge information, and it ends up the VBA :-).
In the first option DELETE key was not problem. Can this be fixed in the option when formulas are created (for example user can not use DELETE key)?

Thanks in advance.

[Kevin#]

This mod prevents non-numeric values being entered

Please Login or Register  to view this content.

[Kevin#]

(More by good fortune than design ) the mod in post#10 prevents the delete key from working!

[besis]

It works. Hope that you dont get bored by thanking you, but here it goes again THANK YOU.

[Kevin#]

Glad it works for you. If you get bored of thanking, then click on the *AddReputation

@cytop said in post#07 "I would suggest to the OP that some kind of user accessible 'Undo' is needed for those times a user mistypes - code that changes a changed cell makes it impossible for an ordinary user to correct an error"

This mod allows you to check that you are putting in the correct value at the time of input

Please Login or Register  to view this content.

[besis]

Hi,

I have have this code writen in 3 specific sheets for selupdating cells.
How can I make this code work on module refering to the specific sheets (for example sheet1, sheet2 and sheet3)?

Thanks for any help I can get.