excel+vba+training - On time

Hi Sam,

I know that one of our trainers, Carlos, has been working on a solution for this problem. I will check in with him today.

Regards, Rich

Hi Samantha

To Run A Macro every hour use the following code. Note that :

1. This code needs to be placed in the ThisWorkbook code area of the VBE. (In the Project window - Double click ThisWorkbook to open it)

2. Paste the code immediately below the Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'This switches off the OnTime function otherwise the workbook would reopen every hour to run the macro

Application.OnTime TheTime, "MyMacro", , False

End Sub

Private Sub Workbook_Open()

Application.OnTime Now + TimeValue("00:60:00), "MyMacro"

End Sub

In the Module your Macro is in you need to make the following changes:

Public TheTime As Date
'This is the variable that holds the time so the procedure can be cancelled on Close

Sub MyMacro()

TheTime = Now + TimeValue("00:60:00") 'Resets the timer to run in 60 mins
Application.OnTime TheTime, "MyMacro" 'Reruns the macro

'YOUR CODE

End Sub

Hope this helps

Carlos

Hi Sam

To run a macro at a predetermined date and time:

On your spreadsheet use a cell eg "A1" to hold the date and time the system is to run the macro. eg. 30/11/07 15:00

Every time the macro runs this will be updated by advancing 7 days.

NB This needs to be physically in the Workbook. If held in a variable it could be be lost when the PC is switched off

This code needs to be placed in the ThisWorkbook code area of the VBE. (In the Project window - Double click This Workbook to open it)

Paste it immediately below the Option Explicit

Private Sub Workbook_Open()

'This sets the date the macro is to run into the system.
'If the date matches today's date the system will run te macro at the required time

RunMacroDate = Worksheets("Sheet1").Range("A1")

Application.OnTime RunMacroDate, "MyMacro"

End Sub

In the Module holding the Macro to be run, you need to make the following changes:

Public RunMacroDate As Variant
'Declared in the Option Explicit area

Sub MyMacro()

'YOURCODE

RunMacroDate = RunMacroDate + 7 'Adds 7 days to the RunMacroDate

Worksheets("Sheet1").Range("A1") = RunMacroDate 'Replaces the RunMacroDate on your worksheet with new value

End Sub

Regards

Carlos