Results 1 to 3 of 3

Restart macro when "another" workbook is opened

Threaded View

  1. #1
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,287

    Restart macro when "another" workbook is opened

    Due to the instability of 2007, and a habit some people have of leaving complex files open for days on end without saving, I had decided on automatically shutting them if workbook not used for 30 minutes, leaving them back at the main menu used to open the workbooks. This works well and requires no interaction from the user even though a warning pop up is given. How can I get the macro to restart after it has run because when the menu is used to re-open files after the event has occurred the macro does not restart.

    In workbook
    Private Sub Workbook_Open()
        
        If MsgBox("OPEN WORKBOOK WILL AUTOMATICALY SAVE AND CLOSE IF NOT USED FOR 30 MINUTES - THIS IS TO PROTECT INFORMATION AND WILL ONLY REQUIRE YOU TO RE-OPEN FILES FROM MAIN MENU IF YOU NEED IT AGAIN - CLICK YES TO PROCEED - CLICK NO TO NOT USE THIS SERVICE", vbYesNo) = vbYes Then
        
    Call SetTime
    Else
        Exit Sub
    End If
    End Sub
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call Disable
    End Sub
    Private Sub Workbook_SheetCalculate(ByVal SH As Object)
    Call Disable
    Call SetTime
    End Sub
    Private Sub Workbook_SheetSelectionChange(ByVal SH As Object, ByVal Target _
    As Excel.Range)
    Call Disable
    Call SetTime
    End Sub
    In module
    Dim DownTime As Date
    Sub SetTime()
    DownTime = Now + TimeValue("00:30:00")
    Application.OnTime DownTime, "ShutDown"
    End Sub
    Sub ShutDown()
    Dim SH As IWshRuntimeLibrary.WshShell
    Dim Res As Long
    Set SH = New IWshRuntimeLibrary.WshShell
    Res = SH.Popup(Text:="THE OPEN WORKBOOKS HAVE NOT BEEN USED FOR 30 MINS AND WILL NOW SAVE & CLOSE AUTOMATICALLY IN 10 SECONDS - SCREEN WILL RETURN TO MAIN MENU", secondstowait:=10, _
        Title:="CONTENT SECURITY PROTECTION", Type:=vbOKOnly)
    
    Dim WB As Workbook
     For Each WB In Workbooks
     If Not WB.Name = ThisWorkbook.Name Then
     WB.Close SaveChanges:=True
     End If
     Next WB
    End Sub
    Sub Disable()
    
    On Error Resume Next
    Application.OnTime EarliestTime:=DownTime, Procedure:="ShutDown", _
    Schedule:=False
    End Sub
    any pointers appreciated

    requires widows script host object model to be run in "tools" - "references" of vba in menu workbook
    Last edited by nigelog; 08-01-2013 at 11:01 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Excel 2010 -- "Visual Basic" "Macros" and "Record Macro" all disabled.
    By NicholasL in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-07-2017, 06:11 AM
  2. How can i copy value from "HTMLText"(EMBED("Forms.HTML:Text","")),using Macro
    By andrewyang in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-18-2010, 12:47 AM
  3. Replies: 0
    Last Post: 01-04-2006, 06:55 PM
  4. [SOLVED] Call a sub statement in "Personal Macro Workbook" from "ThisWorkbo
    By QC Coug in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-26-2005, 03:05 PM
  5. Macro working in "This Workbook", but not while in "Personal.xls"
    By markx in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-07-2005, 01:06 PM

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