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?


"Joel" wrote:

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


"jtfalk" wrote:

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