Results 1 to 5 of 5

Workbook automatically opens after closing

Threaded View

SJB Workbook automatically opens... 01-06-2010, 12:11 PM
blane245 Re: Workbook automatically... 01-08-2010, 04:48 PM
jaslake Re: Workbook automatically... 01-08-2010, 05:30 PM
SJB Re: Workbook automatically... 01-08-2010, 05:49 PM
jaslake Re: Workbook automatically... 01-08-2010, 09:12 PM
  1. #1
    Registered User
    Join Date
    12-08-2009
    Location
    Illinois
    MS-Off Ver
    Excel 2007
    Posts
    19

    Workbook automatically opens after closing

    I have a workbook on a share drive that is used by multiple people. In the event that someone leaves the workbook open after using it, I have a timer function that pops up a splash screen after 4 minutes of inactivity which states, "This workbook will close in 1 minute if there is no further activity". If there is no further activity in that minute, the workbook closes.

    The code works fine...unless the workbook is manually closed in that minute between the splash screen and when the timer would have closed the workbook. If that happens, the workbook closes normally and then briefly reopens and closes a minute later when the timer would have closed the workbook.

    Is there any code to prevent this?

    Public RunWhen As Double
    Public Const cRunIntervalSeconds = 300 'FIVE minutes
    Public Const cRunWhat = "CloseTheWorkbook"
    
    Public SplashRunWhen As Double
    Public Const cSplashRunIntervalSeconds = 240 'FOUR minutes
    Public Const cSplashRunWhat = "SplashScreen_Open"
    
    
    Sub Timer()
        RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
        Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, Schedule:=True
        
        SplashRunWhen = Now + TimeSerial(0, 0, cSplashRunIntervalSeconds)
        Application.OnTime EarliestTime:=SplashRunWhen, Procedure:=cSplashRunWhat, Schedule:=True
    End Sub
    
    
    Sub StopTimer()
        On Error Resume Next
        Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, Schedule:=False
        Application.OnTime EarliestTime:=SplashRunWhen, Procedure:=cSplashRunWhat, Schedule:=False
    End Sub
    
    
    Sub CloseTheWorkbook()
    With ActiveWorkbook
        .RunAutoMacros xlAutoClose
        .Save
        .Close
    End With
    End Sub
    
    
    Sub SplashScreen_Open()
        SplashScreen.Show
    End Sub
    Last edited by SJB; 01-11-2010 at 12:47 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1