Timed macros

[LiAD]

Hi,

I have an excel sheet with a very simple macro to +1 to a cell every time
somebody clicks a particular form button. I would like to set a time limit
of 5 minutes, after which the cell that has just been updated would be reset
to zero following the last touch of the input button.

Is this possible with excel macros?

 

[Bernie Deitrick]

If you mean that the value should be set to zero if it is click after not having been clicked for at
least 5 minutes, then

Option Explicit
Public myT As Date

Sub PlusOneReset()
If myT = 0 Then myT = Now
If Now - myT > 5/1440 Then
Range("A2").Value = 0
Else
Range("A2").Value = Range("A2").Value + 1
End If
End Sub

HTH,
Bernie
MS Excel MVP

 

[LiAD]

Hi,

I can't get the reset to work. I changed it from 5 to 1 min just to test
it. The entire macro I'm using is below. Do I need something else?

Thanks a lot for your help

Sub NewMacro1()
'reset L6 to 0
Sheet2.Range("L6").Value = 0

With Sheet2
If myT = 0 Then myT = Now
If Now - myT > 1 / 14400 Then
Range("L6").Value = 0
Else
Range("L6").Value = Range("L6").Value + 1
End If
End With


'Add 1 to I38
With Sheet1
If IsNumeric(.Range("J38").Value) Then
..Range("J38").Value = .Range("J38") + 1
Else
MsgBox "J38 on sheet1 isn't a number!"
End If
End With
End Sub

 

[Bernie Deitrick]

Option Explicit
Public myT As Date
Sub NewMacro1()

If myT = 0 Then myT = Now

With Sheet2
If Now - myT > 1 / 1440 Then 'Note - use 1440 (60*24), not 14400
.Range("L6").Value = 0
Else
.Range("L6").Value = .Range("L6").Value + 1
End If
myT = Now
End With

'Add 1 to I38
With Sheet1
If IsNumeric(.Range("J38").Value) Then
.Range("J38").Value = .Range("J38") + 1
Else
MsgBox "J38 on sheet1 isn't a number!"
End If
End With
End Sub


HTH,
Bernie
MS Excel MVP

 

[LiAD]

Sorry for asking again.

I cannot get the reset to work or the value in L6 to increase by one when I
push the macro. The value in J38 is increasing by one and the rest of the
macro is working, just not the part that deals with cell L6.

 

[LiAD]

Got it working.

Great.

Thanks a lot for your help