Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

excel+training - vb times

Forum home » Delegate support and help forum » Microsoft Excel Training and help » excel+training - VB Times

excel+training - VB Times

ResolvedVersion Standard

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?

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

Edited on Fri 23 Nov 2007, 14:13

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

Excel tip:

Editing Formulas in Excel Cells

Although people like to edit a formula in the Formula bar, you can also edit a formula in the cell.

To do this select the cell and press "F2". This puts Excel into Edit mode, and you can move around in the cell and make any necessary changes.

Double-clicking the cell also puts Excel in Edit mode.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.1 secs.