Closing running sub from a different one

[jtfalk]

I have a sub that starts a timmer and checks to see if you are there or
closes the workbook. That works great. if I close the workbook the old
fashioned way ythe CloseMe sub is still running and opens the workbook back
up. I need to put something in Sub Workbook_BeforeClose to stop the Sub
CloseMe(). I posted this earlier but it was difficult to understand waht i
was saying. i hope this is easier.

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




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
Sheets("Dept. a").Visible = xlSheetVeryHidden
Sheets("Dept. b").Visible = xlSheetVeryHidden
Sheets("Dept. c").Visible = xlSheetVeryHidden
Sheets("ALL DEPARTMENT GRAPHS").Visible = xlSheetVeryHidden
Sheets("MACROS").Select
ThisWorkbook.Save
ThisWorkbook.Close
End If
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sht As Worksheet
Application.ScreenUpdating = False
Sheets("Dept. a").Visible = xlSheetVeryHidden
Sheets("Dept. b").Visible = xlSheetVeryHidden
Sheets("Dept. c").Visible = xlSheetVeryHidden
Sheets("ALL DEPARTMENT GRAPHS").Visible = xlSheetVeryHidden
Sheets("MACROS").Select
Application.ScreenUpdating = True
ThisWorkbook.Save
!!!!!!!!!STOP THE CLOSEME SUB!!!!!!!!
ThisWorkbook.Close
End Sub

 

[Joel]

You need to make SH a public variable so you can shut down. Then adding
set SH = Nothing in the before close routine.


Dim SH As IWshRuntimeLibrary.WshShell

Public Sub CloseMe()
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
Sheets("Dept. a").Visible = xlSheetVeryHidden
Sheets("Dept. b").Visible = xlSheetVeryHidden
Sheets("Dept. c").Visible = xlSheetVeryHidden
Sheets("ALL DEPARTMENT GRAPHS").Visible = xlSheetVeryHidden
Sheets("MACROS").Select
ThisWorkbook.Save
ThisWorkbook.Close
End If
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sht As Worksheet
Application.ScreenUpdating = False
Sheets("Dept. a").Visible = xlSheetVeryHidden
Sheets("Dept. b").Visible = xlSheetVeryHidden
Sheets("Dept. c").Visible = xlSheetVeryHidden
Sheets("ALL DEPARTMENT GRAPHS").Visible = xlSheetVeryHidden
Sheets("MACROS").Select
Application.ScreenUpdating = True
ThisWorkbook.Save
!!!!!!!!!STOP THE CLOSEME SUB!!!!!!!!
set SH = Nothing
ThisWorkbook.Close
End Sub

 

[Barb Reinhardt]

Have you tried using

Application.EnableEvents = FALSE

Keep in mind that if you do this, it'll turn off events until you turn them
back on either manually, in another procedure, or you restart Excel.

HTH,
Barb Reinhardt

 

[jtfalk]

Okay - now I know what i have. It is the:

Sub Auto_Open()
Application.OnTime Now() + TimeValue("00:00:50"), "CloseMe"
End Sub

So if I let the first 50 seconds go by and then the closeme loop starts and
asks me if I am there and I say yes and then close it down it works fine with
no restart. The problem is if i shut it down in the first 50 seconds then the
loop hasn't started yet but will be called (unless excel is closed completely
but we assume that a person will have multiple workbooks open at the same
time.) So I need to put the call to the CloseMe sub in the Private Sub
Workbook_Open()

So can i put:

Private Sub Workbook_Open()
Dim sht As Worksheet
Application.ScreenUpdating = False
Sheets("Dept. a").Visible = True
Sheets("Dept. b").Visible = True
Sheets("Dept. c").Visible = True
Sheets("ALL DEPARTMENT GRAPHS").Visible = True
Sheets("MACROS").Visible = True
Sheets("Dept. a").Select
Application.ScreenUpdating = True
CloseMe()
End Sub

Then in the CloseMe I need to skip the immediate asking of the question some
type of delay or pause. I can not use application.wait becasue it makes the
whole application wait so you can not do anything.

Any Suggestions?