Forum home » Delegate support and help forum » Microsoft Excel Training and help » excel+training - VB Times
|
|
|
The UK's most regular Training information: See also: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
excel+training - VB Times
Resolved · Low Priority · Version Standard
Reply Fri 16 Nov 2007, 16:37Delegate Suzie said...
Suzie has attended:
Excel VBA Intro Intermediate course
VB Times
How can you get Excel to check the time for when an hour has passed?
For upcoming training course dates see: Pricing & availability
RE: 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
Inform user One Hour has passed
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
|