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?

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" wrote in message
...
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?

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

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" wrote in message
...
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?

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" wrote in message
...
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" wrote:

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" wrote in message
...
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?

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.

"Bernie Deitrick" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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?

Got it working.

Great.

Thanks a lot for your help

"Bernie Deitrick" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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?