excel+training - VB Times

Suzie

The code below records the time the Workbook was opened and will inform you when an Hour has passed.

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

Paste it immediately below the Option Explicit


Dim StartTime As Date
Dim NewTime As Double

Private Sub Workbook_Activate()

StartTime = Now()

End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

NewTime = Now() - StartTime

If NewTime >= 0.041666667 Then 'This is the numeric value for 1 hour

MsgBox "An hour has passed"

End If

End Sub


Hope this helps.

Carlos

Hi Suzie

Further to the code I gave you earlier the code below is simpler and will give you a message every hour. 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 RunMacroTime, "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 RunMacroTime As Date
'This is the variable that holds the time so the procedure can be cancelled on Close

Sub MyMacro()

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

MsgBox "1 Hour Has Passed"

End Sub

Regards

Carlos