Creating "Add/Subtract" cells to modify locked cells...

[Nicholas]

Alright, I hope this makes SOME sort of sense...

Basically what I am trying to do, is to create a locked cell, then add two cells underneath it, one which would be coded specifically for *ADDING* to the value of the locked cell, and one for *SUBTRACTING* from it.

Ideally, the way it would work, is that the user could type in an amount in the "add" cell, which would then populate into the locked cell, automatically, and immediately clearing the "add" cell. In order to subtract from the locked cell, they would have to use the "subtract" cell. Using the "add" cell again would (IDEALLY) add the NEW number they were inputting to the previous number which had already populated the locked cell.

In other words, inserting "10" into the "add" cell would then populate the locked cell with "10." - Inserting "10" into the "add" cell AGAIN would change the locked cell to "20." - The only way to subtract from the locked cell (ideally) would be to use the "subtract" cell.

If this makes any sense, and ANYONE knows of a way (complex or otherwise; though, I doubt sincerely it could possibly be otherwise), please let me know.

Thanks!!

[Bryan Hessey]

Alright, I hope this makes SOME sort of sense...

Basically what I am trying to do, is to create a locked cell, then add two cells underneath it, one which would be coded specifically for *ADDING* to the value of the locked cell, and one for *SUBTRACTING* from it.

Ideally, the way it would work, is that the user could type in an amount in the "add" cell, which would then populate into the locked cell, automatically, and immediately clearing the "add" cell. In order to subtract from the locked cell, they would have to use the "subtract" cell. Using the "add" cell again would (IDEALLY) add the NEW number they were inputting to the previous number which had already populated the locked cell.

In other words, inserting "10" into the "add" cell would then populate the locked cell with "10." - Inserting "10" into the "add" cell AGAIN would change the locked cell to "20." - The only way to subtract from the locked cell (ideally) would be to use the "subtract" cell.

If this makes any sense, and ANYONE knows of a way (complex or otherwise; though, I doubt sincerely it could possibly be otherwise), please let me know.

Thanks!!

On the worksheet tab, rightmouse and select View Code, paste this code into there

Please Login or Register  to view this content.

This assumes B2 = plus, C2 = locked number, D2 = minus
password = password (you will need to change both 'password' to your desired word).

hth
---

[Nicholas]

Thank you, VERY much - that worked almost perfectly (aside from the sheet auto-locking after every cell entry), I simply removed the lock code, and put a manual lock on the sheet through the actual Excel UI. *BUT* - My next problem is how would I go about using this for multiple cells on the same sheet?

EG:

"C4 = SUM, C5 = +, E5 = -; C6 = SUM, C7 = +, E7 = -; etc., etc."

[Bryan Hessey]

Thank you, VERY much - that worked almost perfectly (aside from the sheet auto-locking after every cell entry), I simply removed the lock code, and put a manual lock on the sheet through the actual Excel UI. *BUT* - My next problem is how would I go about using this for multiple cells on the same sheet?

EG:

"C4 = SUM, C5 = +, E5 = -; C6 = SUM, C7 = +, E7 = -; etc., etc."

You would need to amend the

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

etc, and then setup seperate code sets for each 3-cell set.

Add

Please Login or Register  to view this content.

etc.


---

[Nicholas]

Alright, I've got the multiple "+/-/=" cells working properly, as well as a "Date Entered" function working... *HOWEVER* I can't seem to figure out how to lock ALL these cells, except for the "Add to" and "Subtract from" cells! =\

I'm sure the bit of code that was posted first to auto-unlock and re-lock the cells before and after the code will work, but I'm not sure where exactly it belongs, now that there are multiple instances of the "+/-/=" cells, as well as there being the "Date Entered" auto-populating cells.

Basically, when I try to just lock the cells in the Excel UI, setting them as protected and then locking the sheet, the code has an error, because it's trying to modify cells that are locked, obviously... But how do I work around this, so the "+/-" cells stay unlocked, but EVERYTHING ELSE can be locked, and still populated with: What was entered in the "+" tab, What was entered in the "-" tab, and WHEN either tab was last modified.

Here's what the code looks like so far, lacking, obviously, the "unlocking/locking" code bits...

Please Login or Register  to view this content.

Any help would be greatly appreciated, they're imaginining, here at work, that I'm some "Excel Guru" while you all understand already that I am "Mediocre" at best.

Thanks again, in advance!

[Bryan Hessey]

Alright, I've got the multiple "+/-/=" cells working properly, as well as a "Date Entered" function working... *HOWEVER* I can't seem to figure out how to lock ALL these cells, except for the "Add to" and "Subtract from" cells! =\

I'm sure the bit of code that was posted first to auto-unlock and re-lock the cells before and after the code will work, but I'm not sure where exactly it belongs, now that there are multiple instances of the "+/-/=" cells, as well as there being the "Date Entered" auto-populating cells.

Basically, when I try to just lock the cells in the Excel UI, setting them as protected and then locking the sheet, the code has an error, because it's trying to modify cells that are locked, obviously... But how do I work around this, so the "+/-" cells stay unlocked, but EVERYTHING ELSE can be locked, and still populated with: What was entered in the "+" tab, What was entered in the "-" tab, and WHEN either tab was last modified.

Here's what the code looks like so far, lacking, obviously, the "unlocking/locking" code bits...

Please Login or Register  to view this content.

Any help would be greatly appreciated, they're imaginining, here at work, that I'm some "Excel Guru" while you all understand already that I am "Mediocre" at best.

Thanks again, in advance!

I like to keep it simple, (then I can understand it)

I used the rightmouse Format Cells on the worksheet, and removed those cells that I didn't want locked, in your case that should be every cell except the 'value' cells. Then just protect the sheet.

Where to, . . as soon as you detect a need, and re-protect just prior to exiting that, so I would unlock just before 'Application.EnableEvents = False' and re-apply just after the 'True'.

hth
---
added also I would probably go

Please Login or Register  to view this content.

- you're going to do the plus & minus, why bother re-testing.
you can replace the rest of that code with

Please Login or Register  to view this content.

or similar. - perhaps set offcol on Target.column = B C D or E to 2 1 0 -1 etc

hth
---

[Nicholas]

Thanks! That worked perfectly, used your (exponentially cleaner) code in place of my own, and added the "password" lines back in, unlocking after "Application.ScreenUpdating = False" and I moved the re-locking after the "Date" script (as it was having an error trying to post a date in a locked cell).

Perfect! - Thank you very much for your patience and help!

[Bryan Hessey]

Thanks! That worked perfectly, used your (exponentially cleaner) code in place of my own, and added the "password" lines back in, unlocking after "Application.ScreenUpdating = False" and I moved the re-locking after the "Date" script (as it was having an error trying to post a date in a locked cell).

Perfect! - Thank you very much for your patience and help!

Wise move

Good to see it works for you, and thanks for the response.
---