| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Closing running sub from a different one
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Closing running sub from a different one
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Closing running sub from a different one
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 "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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Closing running sub from a different one
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel keeps running after closing on server | Excel Programming | |||
| Auto running a macro on closing | Excel Discussion (Misc queries) | |||
| Error when closing workbooks after running .net code | Excel Programming | |||
| Running a macro Upon Closing | Excel Programming | |||
| Closing the Userform when macro is running | Excel Programming | |||
Linear Mode
