Closing down running macros at the file close

[jtfalk]

i have a file that I have put in an auto-open on that asks if you are there
after 10 minutes, if you do not click yes it saves and closes the workbook
but not excel (incase there is another workbook open). So the problem is if
there are 2 or 3 workbooks open if I close the workbook myslf after 10
minutes it actually reopens the disable/enable macro box and then will show
my yes/no are you there box. On close what can I put in to shut off all
macro's in the workbook?
On close I need to STOP the Application.OnTime Now() +
TimeValue("00:00:50"), "CloseMe"

Sub Auto_Open()
LogInformation ThisWorkbook.Name & " opened by " & Application.UserName &
" " & Format(Date, "yyyy-mm-dd") & " " & Format(Time, "hh:mm")
Application.OnTime Now() + TimeValue("00:00:50"), "CloseMe"
End Sub
'requires reference to "Windows Script Host Object Model"
Public Sub CloseMe()
Dim SH As IWshRuntimeLibrary.WshShell
Dim Res As Long

Set SH = New IWshRuntimeLibrary.WshShell
Res = SH.Popup(Text:="Are you still there? Please click Yes or this file
will close in 2 minutes", secondstowait:=2, _
Title:="Active", Type:=vbYesNo)
If Res = vbYes Then
Application.OnTime Now() + TimeValue("00:00:50"), "CloseMe"
Else
ThisWorkbook.Save
ThisWorkbook.Close
End If
End Sub

 

[FSt1]

hi
try putting an Exit Sub after ThisWorkbook.Close

Regards
FSt1

 

[jtfalk]

Sorry, I forgot to add that on close thisis the code - so somewhere here I
need to stop the timing for the public sub Closeme():

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ScreenUpdating = False

Sheets("home").Visible = xlSheetVeryHidden
Sheets("MACROS").Select


Application.ScreenUpdating = True
ThisWorkbook.Save
ThisWorkbook.Close
End Sub

 

[FSt1]

hi
now you have me confused but i still think the you can kill the closeme sub
with an exit sub in the else part of that sub even if you don't close the
file at that time so that the before sub can fire. accually it looks like you
could add the code in the before close sub to the else part of the closeme
sub.

regards
FSt1

 

[Gord Dibben]

I think you have to stop the timer.

See Chip Pearson's site for hints, tips and code for OnTime

http://www.cpearson.com/excel/OnTime.aspx


Gord Dibben MS Excel MVP

 

[jtfalk]

I went to the timed closing of a workbook at thsis site and put in the
follwoing code but I keep getting a sub or function not defined at the
workbook_open, workbook_close.Is there an addin that I need to have or what
can I do to get it to work? If this works it will eliminate my other issue.

In a standard module of the VBA project, paste the following code:

Public RunWhen As Double
Public Const NUM_MINUTES = 10

Public Sub SaveAndClose()
ThisWorkbook.Close savechanges:=True
End Sub

Change the value of NUM_MINUTES to the number of minutes you want to leave
the workbook unattended before closing.

In the ThisWorkbook module, paste the following code:

Private Sub Workbook_Open()
On Error Resume Next
Application.OnTime RunWhen, "SaveAndClose", , False
On Error GoTo 0
RunWhen = Now + TimeSerial(0, NUM_MINUTES, 0)
Application.OnTime RunWhen, "SaveAndClose", , True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnTime RunWhen, "SaveAndClose", , False
On Error GoTo 0
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
Application.OnTime RunWhen, "SaveAndClose", , False
On Error GoTo 0
RunWhen = Now + TimeSerial(0, NUM_MINUTES, 0)
Application.OnTime RunWhen, "SaveAndClose", , True
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)

On Error Resume Next
Application.OnTime RunWhen, "SaveAndClose", , False
On Error GoTo 0
RunWhen = Now + TimeSerial(0, NUM_MINUTES, 0)
Application.OnTime RunWhen, "SaveAndClose", , True

End Sub