excel+training - vb times

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

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

excel+training - VB Times

resolvedResolved · Low Priority · Version 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

 

Training courses

 

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
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Add Text to Displayed Numbers in Excel 2010

To add text to a number in a cell, you need to go to the Home tab on the Ribbon, and click on the Cells group. Select Format Cells from the drop down menu then Custom from the Category list. In the Type box select General. After the word General, enter a space, then opening quotation marks, then the word you want to type and then closing quotation marks. Click on OK and you have your text!

View all Excel hints and tips


Server loaded in 0.09 secs.