Hi,

I have the following VB Coding which activates macros on a regula
basis automatically, if Excel is left open.

Sub RunOnTime()
Application.OnTime Date + TimeSerial(15,0,0), "Refresh"
End Sub

Refresh is the macro which is being run.

The RunOnTime macro, activates the Refresh macro at 3pm every day.
Is there a way, or does anyone know, how I can get a macro similar t
this to run on a weekly basis rather than a daily basis as this on
currently does?

Thanks In Advance
Kiz

--
Message posted from http://www.ExcelForum.com

Hi Kiza,

Application.OnTime Date + 7 + TimeSerial(15,0,0), "Refresh"

should do the trick (assuming you want to refresh at 3:00pm in seven days
time).

Just FYI Date returns the number of days since 31 December 1899, so you can
just do ordinary integer arithmetic with it.

HTH

Peter Beach

"kiza " wrote in message
...
Hi,

I have the following VB Coding which activates macros on a regular
basis automatically, if Excel is left open.

Sub RunOnTime()
Application.OnTime Date + TimeSerial(15,0,0), "Refresh"
End Sub

Refresh is the macro which is being run.

The RunOnTime macro, activates the Refresh macro at 3pm every day.
Is there a way, or does anyone know, how I can get a macro similar to
this to run on a weekly basis rather than a daily basis as this one
currently does?

Thanks In Advance
Kiza


---
Message posted from http://www.ExcelForum.com/

Thanks for that.

What you have shown me will allow the macro to be run in 7 days fro
the date given. I would like the macro to run once a week until it i
stopped automatically.

This section of the vb coding:

Application.OnTime Date + 7 + TimeSerial(15,0,0), "Refresh"

: will only allow the macro to run again in 7 days time. I will the
want it to run 7 days after that and then 7 days after that unti
stopped.

Is that possible

--
Message posted from http://www.ExcelForum.com

Hi Kiza,

In the Refresh routine, simply call:

Application.OnTime Date + 7 + TimeSerial(15,0,0), "Refresh"

IOW the Refresh routine itself sets the next time it should be called.

Regards,

Peter Beach

"kiza " wrote in message
...
Thanks for that.

What you have shown me will allow the macro to be run in 7 days from
the date given. I would like the macro to run once a week until it is
stopped automatically.

This section of the vb coding:

Application.OnTime Date + 7 + TimeSerial(15,0,0), "Refresh"

: will only allow the macro to run again in 7 days time. I will then
want it to run 7 days after that and then 7 days after that until
stopped.

Is that possible?


---
Message posted from http://www.ExcelForum.com/

Thanks...

I have the following coding:

Dim dtScheduledTime As Date
Sub Update()
ActiveCell.Offset(1, 0).Activate
Selection.Interior.ColorIndex = 3
dtScheduledTime = Now + TimeSerial(0, 0, 2)
Application.OnTime dtScheduleTime, "Refresh"
End Sub

Sub StopRefresh()
Application.OnTime dtScheduledTime, "Refresh", , True
End Sub

Sub Refresh()
ActiveCell.Offset(1, 0).Activate
Selection.Interior.ColorIndex = 3
End Sub

Sub RunOnTime()
Application.OnTime Date + TimeSerial(0, 0, 2), "Refresh"
End Sub

For some reason, Im not sure what it is, but I cannot get it to repea
itself. Have I forgotten to do something

--
Message posted from http://www.ExcelForum.com

Hi Kiza,
A really simple way to do this is create a new workbook with an autorun()
macro that opens your workbook and runs the macro.
Then create a task with windows schedule task feature to run the new
workbook.

--
John
johnf 202 at hotmail dot com


"kiza " wrote in message
...
| Hi,
|
| I have the following VB Coding which activates macros on a regular
| basis automatically, if Excel is left open.
|
| Sub RunOnTime()
| Application.OnTime Date + TimeSerial(15,0,0), "Refresh"
| End Sub
|
| Refresh is the macro which is being run.
|
| The RunOnTime macro, activates the Refresh macro at 3pm every day.
| Is there a way, or does anyone know, how I can get a macro similar to
| this to run on a weekly basis rather than a daily basis as this one
| currently does?
|
| Thanks In Advance
| Kiza
|
|
| ---
| Message posted from http://www.ExcelForum.com/
|

Hi Kiza,

Change Refresh to read:

Sub Refresh()
ActiveCell.Offset(1, 0).Activate
Selection.Interior.ColorIndex = 3
dtScheduledTime = Now + TimeSerial(0, 0, 2)
Application.OnTime dtScheduleTime, "Refresh"
End Sub

HTH

Peter Beach

"kiza " wrote in message
...
Thanks...

I have the following coding:

Dim dtScheduledTime As Date
Sub Update()
ActiveCell.Offset(1, 0).Activate
Selection.Interior.ColorIndex = 3
dtScheduledTime = Now + TimeSerial(0, 0, 2)
Application.OnTime dtScheduleTime, "Refresh"
End Sub

Sub StopRefresh()
Application.OnTime dtScheduledTime, "Refresh", , True
End Sub

Sub Refresh()
ActiveCell.Offset(1, 0).Activate
Selection.Interior.ColorIndex = 3
End Sub

Sub RunOnTime()
Application.OnTime Date + TimeSerial(0, 0, 2), "Refresh"
End Sub

For some reason, Im not sure what it is, but I cannot get it to repeat
itself. Have I forgotten to do something?


---
Message posted from http://www.ExcelForum.com/