Hi Experts,
I'd like check modification in worksheet. If haven't
change in sheet for each intervals (e.g. each 30 second)
then get info "Will you still work?" vbYesNo.
Yes (still work) , No (close workbook).
I have written follow script but it function badly:

(in body worksheet)
Public dd As Date
Sub Worksheet_Change(ByVal Target As Range)
dd = Now
Application.OnTime dd + TimeValue("00:00:30"), "check"
End Sub

(in body module)
Public dd As Date
Public A as Date
Sub check()
A = Now
If A - dd < 30 Then ' although dd is public variable dd =
0 , why ???

....
End If
End Sub

There are some solutions in NET, but can anyone provide
full correct VBA script?


I've excel 2k.

Kindly regards
Mark

Hi
you have defined this variabl two times. Therefor in you
check procedure it used the variable in this module (this
is not initialized). Not tested but try removing on of the
variable declarations

-----Original Message-----
Hi Experts,
I'd like check modification in worksheet. If haven't
change in sheet for each intervals (e.g. each 30 second)
then get info "Will you still work?" vbYesNo.
Yes (still work) , No (close workbook).
I have written follow script but it function badly:

(in body worksheet)
Public dd As Date
Sub Worksheet_Change(ByVal Target As Range)
dd = Now
Application.OnTime dd + TimeValue("00:00:30"), "check"
End Sub

(in body module)
Public dd As Date
Public A as Date
Sub check()
A = Now
If A - dd < 30 Then ' although dd is public variable dd =
0 , why ???

....
End If
End Sub

There are some solutions in NET, but can anyone provide
full correct VBA script?


I've excel 2k.

Kindly regards
Mark

.

Hi Frank,
Double declare statement there isn't reason my problem.
Mayby my code isn't correct.
I still looking for vba script..
Regards

-----Original Message-----
Hi
you have defined this variabl two times. Therefor in you
check procedure it used the variable in this module (this
is not initialized). Not tested but try removing on of
the
variable declarations

-----Original Message-----
Hi Experts,
I'd like check modification in worksheet. If haven't
change in sheet for each intervals (e.g. each 30 second)
then get info "Will you still work?" vbYesNo.
Yes (still work) , No (close workbook).
I have written follow script but it function badly:

(in body worksheet)
Public dd As Date
Sub Worksheet_Change(ByVal Target As Range)
dd = Now
Application.OnTime dd + TimeValue("00:00:30"), "check"
End Sub

(in body module)
Public dd As Date
Public A as Date
Sub check()
A = Now
If A - dd < 30 Then ' although dd is public variable dd
=
0 , why ???

....
End If
End Sub

There are some solutions in NET, but can anyone provide
full correct VBA script?


I've excel 2k.

Kindly regards
Mark

.

.

you question was:

' although dd is public variable dd =
0 , why ???


The reason is exactly that you have declared the variable twice and are
working with two different variables, just as Frank has stated.

You should be able to get a working solution by paying attention to the
example presented by Chip Pearson

http://www.cpearson.com/excel/ontime.htm

and understanding the rules of scope.

--
Regards,
Tom Ogilvy

"Mark" wrote in message
...
Hi Frank,
Double declare statement there isn't reason my problem.
Mayby my code isn't correct.
I still looking for vba script..
Regards

-----Original Message-----
Hi
you have defined this variabl two times. Therefor in you
check procedure it used the variable in this module (this
is not initialized). Not tested but try removing on of
the
variable declarations

-----Original Message-----
Hi Experts,
I'd like check modification in worksheet. If haven't
change in sheet for each intervals (e.g. each 30 second)
then get info "Will you still work?" vbYesNo.
Yes (still work) , No (close workbook).
I have written follow script but it function badly:

(in body worksheet)
Public dd As Date
Sub Worksheet_Change(ByVal Target As Range)
dd = Now
Application.OnTime dd + TimeValue("00:00:30"), "check"
End Sub

(in body module)
Public dd As Date
Public A as Date
Sub check()
A = Now
If A - dd < 30 Then ' although dd is public variable dd
=
0 , why ???

....
End If
End Sub

There are some solutions in NET, but can anyone provide
full correct VBA script?


I've excel 2k.

Kindly regards
Mark

.

.

Even declaring dd as Public will not work if you put it in the worksheet
module as you seem to suggest. In this case, you need to check it in the
'check' procedure like this

If A - Sheet1.dd < 30 Then
or whatever the worksheet codename is.

But why do you need to check itr, the check routine will only fire after 30
seconds. What you nedd to do is have a routine that traps entry and cencel
the OnTime when that occurs, and then re-start it.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Mark" wrote in message
...
Hi Frank,
Double declare statement there isn't reason my problem.
Mayby my code isn't correct.
I still looking for vba script..
Regards

-----Original Message-----
Hi
you have defined this variabl two times. Therefor in you
check procedure it used the variable in this module (this
is not initialized). Not tested but try removing on of
the
variable declarations

-----Original Message-----
Hi Experts,
I'd like check modification in worksheet. If haven't
change in sheet for each intervals (e.g. each 30 second)
then get info "Will you still work?" vbYesNo.
Yes (still work) , No (close workbook).
I have written follow script but it function badly:

(in body worksheet)
Public dd As Date
Sub Worksheet_Change(ByVal Target As Range)
dd = Now
Application.OnTime dd + TimeValue("00:00:30"), "check"
End Sub

(in body module)
Public dd As Date
Public A as Date
Sub check()
A = Now
If A - dd < 30 Then ' although dd is public variable dd
=
0 , why ???

....
End If
End Sub

There are some solutions in NET, but can anyone provide
full correct VBA script?


I've excel 2k.

Kindly regards
Mark

.

.

Hi Bob,
I not very well vba, yet.
My problem is how write code check whether worksheet have
used since eg. 30 second from last change.
If worksheet haven't used since 30 second then
msgbox "Will you still work?" vbYesNo. If NO then
activeworkbook close, if Yes then close MSgbox.
Do you know some smart code?

Thanks for any help.
Mark


-----Original Message-----
Even declaring dd as Public will not work if you put it
in the worksheet
module as you seem to suggest. In this case, you need to
check it in the
'check' procedure like this

If A - Sheet1.dd < 30 Then
or whatever the worksheet codename is.

But why do you need to check itr, the check routine will
only fire after 30
seconds. What you nedd to do is have a routine that traps
entry and cencel
the OnTime when that occurs, and then re-start it.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Mark" wrote in message
...
Hi Frank,
Double declare statement there isn't reason my problem.
Mayby my code isn't correct.
I still looking for vba script..
Regards

-----Original Message-----
Hi
you have defined this variabl two times. Therefor in
you
check procedure it used the variable in this module
(this
is not initialized). Not tested but try removing on of
the
variable declarations

-----Original Message-----
Hi Experts,
I'd like check modification in worksheet. If haven't
change in sheet for each intervals (e.g. each 30
second)
then get info "Will you still work?" vbYesNo.
Yes (still work) , No (close workbook).
I have written follow script but it function badly:

(in body worksheet)
Public dd As Date
Sub Worksheet_Change(ByVal Target As Range)
dd = Now
Application.OnTime dd + TimeValue("00:00:30"), "check"
End Sub

(in body module)
Public dd As Date
Public A as Date
Sub check()
A = Now
If A - dd < 30 Then ' although dd is public variable
dd
=
0 , why ???

....
End If
End Sub

There are some solutions in NET, but can anyone
provide
full correct VBA script?


I've excel 2k.

Kindly regards
Mark

.

.



.

Yes, but there are quite a few component parts. Would you like me to knock
you up a sample workbook and mail it to you? You can then ask any questions
about bits you do not understand.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Mark" wrote in message
...
Hi Bob,
I not very well vba, yet.
My problem is how write code check whether worksheet have
used since eg. 30 second from last change.
If worksheet haven't used since 30 second then
msgbox "Will you still work?" vbYesNo. If NO then
activeworkbook close, if Yes then close MSgbox.
Do you know some smart code?

Thanks for any help.
Mark


-----Original Message-----
Even declaring dd as Public will not work if you put it
in the worksheet
module as you seem to suggest. In this case, you need to
check it in the
'check' procedure like this

If A - Sheet1.dd < 30 Then
or whatever the worksheet codename is.

But why do you need to check itr, the check routine will
only fire after 30
seconds. What you nedd to do is have a routine that traps
entry and cencel
the OnTime when that occurs, and then re-start it.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Mark" wrote in message
...
Hi Frank,
Double declare statement there isn't reason my problem.
Mayby my code isn't correct.
I still looking for vba script..
Regards

-----Original Message-----
Hi
you have defined this variabl two times. Therefor in
you
check procedure it used the variable in this module
(this
is not initialized). Not tested but try removing on of
the
variable declarations

-----Original Message-----
Hi Experts,
I'd like check modification in worksheet. If haven't
change in sheet for each intervals (e.g. each 30
second)
then get info "Will you still work?" vbYesNo.
Yes (still work) , No (close workbook).
I have written follow script but it function badly:

(in body worksheet)
Public dd As Date
Sub Worksheet_Change(ByVal Target As Range)
dd = Now
Application.OnTime dd + TimeValue("00:00:30"), "check"
End Sub

(in body module)
Public dd As Date
Public A as Date
Sub check()
A = Now
If A - dd < 30 Then ' although dd is public variable
dd
=
0 , why ???

....
End If
End Sub

There are some solutions in NET, but can anyone
provide
full correct VBA script?


I've excel 2k.

Kindly regards
Mark

.

.



.