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...

[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

[Tsjallie]

Create a module and paste following code in it:

Public TimerActive As Boolean
Sub SubTract()
    If TimerActive Then
        With ActiveSheet.Cells(1, 1)
            .Value = .Value - 1
        End With
    End If
End Sub

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

Private Sub Worksheet_Change(ByVal Target As Range)

    Select Case ActiveSheet.Cells(1, 1)
        Case Is > 0
            TimerActive = True
            Application.OnTime Now + TimeValue("00:00:01"), "Subtract"
        Case Is <= 0
            TimerActive = False
    End Select
    If Not IsEmpty(ActiveSheet.Cells(2, 1)) Then 'Entering any value in cell A2 will stop the timer
            TimerActive = False
    End If

End Sub

[barc0d3guy]

Create a module and paste following code in it:

Public TimerActive As Boolean
Sub SubTract()
    If TimerActive Then
        With ActiveSheet.Cells(1, 1)
            .Value = .Value - 1
        End With
    End If
End Sub

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

Private Sub Worksheet_Change(ByVal Target As Range)

    Select Case ActiveSheet.Cells(1, 1)
        Case Is > 0
            TimerActive = True
            Application.OnTime Now + TimeValue("00:00:01"), "Subtract"
        Case Is <= 0
            TimerActive = False
    End Select
    If Not IsEmpty(ActiveSheet.Cells(2, 1)) Then 'Entering any value in cell A2 will stop the timer
            TimerActive = False
    End If

End Sub

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 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]

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]

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":

Private Sub Worksheet_Change(ByVal Target As Range)

    Select Case ActiveSheet.Range("F5")
        Case Is > 0
            TimerActive = True
            Application.OnTime Now + TimeValue("00:01:00"), "Subtract"
        Case Is <= 0
            TimerActive = False
    End Select
    If Not IsEmpty(ActiveSheet.Range("A2")) Then 'Entering any value in cell A2 will stop the timer
            TimerActive = False
    End If

End Sub

For Module1:

Public TimerActive As Boolean
Sub SubTract()
    If TimerActive Then
        With ActiveSheet.Range("F5")
            .Value = .Value - 1
        End With
    End If
End Sub

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 ^^

[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.

[millz]

Try:

Sub SubTract()
    If TimerActive Then
        Select Case ActiveSheet.Range("N2")
            Case "Idle": i = 1
            Case "Walking": i = 2
            Case "Running": i = 3
            Case "Jumping": i = 5
        End Select
        With ActiveSheet.Range("F5")
            .Value = .Value - i
        End With
    End If
End Sub

[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.

    If TimerActive Then
        Select Case lcase(ActiveSheet.Range("N2"))
            Case "idle": i = 1
            Case "walking": i = 2
            Case "running": i = 3
            Case "jumping": i = 5
        End Select
        With ActiveSheet.Range("F5")
            .Value = .Value - i
        End With
    End If
End Sub

[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.

Public TimerActive
Sub Subtract()
    If TimerActive Then
        Select Case LCase(ActiveSheet.Range("N2"))
            Case "idle": i = 1
            Case "walking": i = 2
            Case "running": i = 3
            Case "jumping": i = 5
        End Select
        With ActiveSheet.Range("F5")
            .Value = .Value - i
        End With
    End If
End Sub