Add/Subtract value automatically from a cell every minute

[barc0d3guy]

Hi there, i am trying to make a game system work on excel. I guess what i want to do is, every one minute, a value for e.g. "10000" in cell A5, I want it to reduce by 1. Is there a way to do that in excel??

[apo]

You will need to have some kind of trigger to "Start" the timer.. (Button.. WorkBook Open event .. whatever.. ).. and then act accordingly on A5...


More interestly.. what is this "game" you are doing?


Maybe attach a 'dummy' workbook.. and we can help you more...

[Tsjallie]

Create a module and paste following code in it:

Please Login or Register  to view this content.

In the code module of your worksheet paste the following code:

Please Login or Register  to view this content.

[barc0d3guy]

You will need to have some kind of trigger to "Start" the timer.. (Button.. WorkBook Open event .. whatever.. ).. and then act accordingly on A5...


More interestly.. what is this "game" you are doing?


Maybe attach a 'dummy' workbook.. and we can help you more...

It's not a game on excel, but its just a game system, that i am trying on excel to show the team
Here's the file

[barc0d3guy]

Create a module and paste following code in it:

Please Login or Register  to view this content.

In the code module of your worksheet paste the following code:

Please Login or Register  to view this content.

I am really new to VBA on excel, i have never used VBA before with excel. But i guess i did what you asked me to do the in the post above. Tbh i have no clue how it's going to work. What will happen after i have pasted the code? Can you please look at the file above and tell me if I have pasted your code at the right place?

[barc0d3guy]

Hey there, i just tried it again. It works. Thank you so much. I will be needing more help on this as my project progresses. Thanks a ton again

[barc0d3guy]

Hey there, I just realized that, I also have "What if Scenarios" in my excel sheet. So the code above nullfies the action for my "What if Scenarios" on my cell A5 which is the "calorie" count on my sheet. I modified the code a little bit for it to start changing the values in the cell A5. I will attach another version of my excel sheet to explain what's going on. Check sheet4 of the excel file.

[Tsjallie]

I'm surprised that it works, because looking at the workbook you posted in post #4, you pasted the code correctly, but did not adjust the cell to repond to.
The code looks at Activesheet.cells(1,1) which is cell A1. So I think you need to edit did bit.

[barc0d3guy]

I tried editing it. I am not really sure if the attachment on post 7 is the right way to do it. But what i am concerned about now is the "What if scenarios". I don't want the values i have set for that cell in what if scenarios to disappear. I would like the code to deduct from the values obtained from "what if scenarios" every minute on cell A5. If you see the excel sheet without the code, you will see that cell A5 has a formula "=R5+F5", is there a way have the code work with the values obtained in cell A5 from that formula. If that works, than my "what if scenarios" will also work great with the code.

[Tsjallie]

If cell A5 contains a formula then you should not want to decrease the value of that cell, but instead the value in one or more of the cells the formula gets its data from.
In this case either F5 or R5 (or may be both). If you decide to have the worksheet_change event respond to a change in cell F5, then selecting a scenario, will trigger the event and thus start the timer. The What-if-scenarios will not be changed by the code.
Here is the code with the necessary edits:
For the worksheet "Sheet4":

Please Login or Register  to view this content.

For Module1:

Please Login or Register  to view this content.

Used Range instead of Cells for readability.

As I see your sheet I guess you want to do the same trick with the cells G5 to L5 too?

Don't forget to check if the formula in cell A5 is still there.

[barc0d3guy]

Hey, thanks. This works perfect. Now I have some more scenarios like "walking", "running", "idle" and "jumping". If you check my what if scenarios you will see them. So, here's what I want to do, when the "idle" scenario is selected, I want the code to work as it working now, i.e. -1 every minute. On selecting walking, running, jumping, the value deducted should be -2, -3, -5 per min. Is there a way we can have all of this working with the "what if scenarios" when the different states are selected?
here's what I have done

[barc0d3guy]

Bumping this thread ^^

[millz]

Try:

Please Login or Register  to view this content.

[Tsjallie]

Sorry, for not replying sooner. Didn't get a notification or just missed it.
Should be pretty easy. Working on it, but somehow the worksheet_change event is not being fired.
Still trying to find out why. Probably something stupid.

[barc0d3guy]

i tired this, but it didn't really work for me. Thank you for trying though, I really appreciate all the help

[barc0d3guy]

take your time mate. i am really grateful that you are trying to help

[Tsjallie]

Got it!
Millz solution is not working because N2 is in uppercase and the select case in the subtract() procedure is case-sensitive.
If you alter his solution like this (see edits marked red), it will work.

Please Login or Register  to view this content.

[barc0d3guy]

I tried the above code, it didn't work. Maybe I am doing something wrong. I will attach my excel file with this post

[Tsjallie]

Of course it's not working since the source in module 1 is incomplete and there's a typo in it. See the parts marked red.

Please Login or Register  to view this content.

[barc0d3guy]

Thanks this works. Now the only problem is that it subtract * 2 of the value that is to subtracted depending on the "what if scenarios". As soon as it's one minute, it does what it is suppose to do as per the code, i.e. subtract "x" from the R5 or A5, but then it subtract "x" again within 2 seconds.

[Tsjallie]

I took a look at it this morning. Noticed the phenomenon only when I just started the timer. Didn't happen again.
However I did notice on a web site I had opened at the same time that the ticker bar it shows also was bit faultering.
So I guess it had something to do with some background process stalling the screen refresh, which makes you miss one subtraction.
Checked the code just in case. I don't see any flaws in it.