Wednesday, June 27, 2012

VBA Code for Closing a Workbook after Specified Timeperiod


Sub workbook_open()
Dim start, finish, totaltime, totaltimeinminutes, timeinminutes
Application.DisplayAlerts = True
timeinminutes = 3
    If timeinminutes > 1 Then
    'calculating total remaining time
    totaltimeinminutes = (timeinminutes * 60) - (1 * 60)
    start = Timer
    'do other activity for 2 min.s
    Do While Timer < start + totaltimeinminutes
       DoEvents
    Loop
    finish = Timer
    totaltime = finish - start
    Application.DisplayAlerts = False
    MsgBox "This file has been open for " & totaltime / 60 & "minutes,You have 1 minute to save before Excel closes"
    End If
    start = Timer
    Do While Timer < start + (1 * 60)
        DoEvents
    Loop
    finish = Timer
    totaltime = finish - start
    Application.DisplayAlerts = False
    MsgBox "Excel will now close"
    Application.Quit
End Sub

No comments:

Post a Comment