Hi All,
I want to start the macro after 15mins after it stops first time automatically.
As I don't have to go and manually start each time.
Is this possible..????
If yes, then please provide me the code for the same.
Thanks
Sudhir
Hi All,
I want to start the macro after 15mins after it stops first time automatically.
As I don't have to go and manually start each time.
Is this possible..????
If yes, then please provide me the code for the same.
Thanks
Sudhir
[VBA]Application.OnTime Now + TimeValue("00:00:15"), "my_Procedure"[/VBA]
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
I think he wants to do this continually.
If so, create a Public variable called say nTime of type Double
at the end of your my_Procedure macro, add
[vba]
nTime = Now + TimeValue("00:00:15")
Application.OnTime nTime, "my_Procedure"
[/vba]
and then in the BeforeClose event procedure, add
[vba]
Application.OnTime nTime, "my_Procedure",, False
[/vba]
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
I am initiating the macro on a button click. Once it is over then it should automatically start after 15mins every time, till the file is closed.
Do you have a problem with xld's code?
Enjoy,
Tony
---------------------------------------------------------------
Give a man a fish and he'll eat for a day.
Teach him how to fish and he'll sit in a boat and drink beer all day.
I'm (slowly) building my own site: www.WordArticles.com
I am getting the error "Ambiguous name detected: trial" Trial is the name of the procedure.
Here I am displaying a msgbox, which once done should be diplayed after every 15mins.
Please help
Code:
Public Sub trial()
Dim nTime As Double
MsgBox ("Hello")
nTime = Now + TimeValue("00:00:30")
Application.OnTime nTime, "trial"
End Sub
Ambiguous name usually means that you have two subs with the same name....
In the sub you posted in post #6 you have a sub named trial....and you are calling a procedure within it named "trial".....that is probably the problem...
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
so how do you define a procedure?
Thanks Lucas,
I have made some changes after ur advise, still facing the same error.
Now I am calling trial from another procedure. Attaching code for ur reference.
Please let me know how to go about it.
Sub trial()
MsgBox ("Hello")
End Sub
Sub automate()
Dim nTime As Double
trial
nTime = Now + TimeValue("00:00:30")
Application.OnTime nTime, "trial"
End Sub
Runs ok for me ......I changed it to 10 seconds because I'm impatient
run automate and you get the messagebox.
close the messagebox and wait 10 seconds.....messagebox again.
if you're still having problems i would guess that you have another sub named trial or automate....
see attached.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Ok I see....it doesn't keep repeating....
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
U have used "Option Explicit" can you please tell what exactly is it..??
Is your module called trial as well by any chance?
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
BTW the code should be like this.
In a standard code module
[vba]
Public nTime As Double
Sub trial()
MsgBox ("Hello")
nTime = Now + TimeValue("00:00:30")
Application.OnTime nTime, "trial"
End Sub
[/vba]
In ThisWorkbook
[vba]
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime nTime, "trial", , False
End Sub
Private Sub Workbook_Open()
trial
End Sub
[/vba]
This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code
Last edited by Bob Phillips; 10-31-2007 at 05:04 PM.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
You can call it again from the messagbox sub
[VBA]Option Explicit
Public nTime As Double
Sub trial()
MsgBox ("Hello")
automate
End Sub
Sub automate()
'trial
nTime = Now + TimeValue("00:00:05")
Application.OnTime nTime, "trial"
End Sub[/VBA]
Problem is it is not looping Bob...so I just called automate again each time from the sub trial
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Thanks Lucas,
Can u please tell why and when option explicit is used
Hi Guys Thanks.
Now I got to know more than one way of doing this.
Thanks Lucas and Xld
From the help files:Originally Posted by kbsudhir
If used, the Option Explicit statement must appear in a module before any procedures.
When Option Explicit appears in a module, you must explicitly declare all variables using the Dim, Private, Public, ReDim, or Static statements. If you attempt to use an undeclared variable name, an error occurs at compile time.
If you don't use the Option Explicit statement, all undeclared variables are of Variant type unless the default type is otherwise specified with a Deftype statement.
Note Use Option Explicit to avoid incorrectly typing the name of an existing variable or to avoid confusion in code where the scope of the variable is not clear.
It helps avoid misnamed variables, etc. by throwing an error.....
It is a good code practice
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
When to use Option Explicit......always
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Thanks once again