Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Closing down running macros at the file close
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Closing down running macros at the file close
hi
try putting an Exit Sub after ThisWorkbook.Close Regards FSt1 "jtfalk" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Closing down running macros at the file close
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" wrote: hi try putting an Exit Sub after ThisWorkbook.Close Regards FSt1 "jtfalk" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Closing down running macros at the file close
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 "jtfalk" wrote: 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" wrote: hi try putting an Exit Sub after ThisWorkbook.Close Regards FSt1 "jtfalk" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Closing down running macros at the file close
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 On Thu, 1 Oct 2009 11:59:03 -0700, jtfalk wrote: 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" wrote: hi try putting an Exit Sub after ThisWorkbook.Close Regards FSt1 "jtfalk" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Closing down running macros at the file close
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 "Gord Dibben" wrote: 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 On Thu, 1 Oct 2009 11:59:03 -0700, jtfalk wrote: 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" wrote: hi try putting an Exit Sub after ThisWorkbook.Close Regards FSt1 "jtfalk" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Closing a file with macros | Excel Programming | |||
closing a variable file name with macros | Excel Programming | |||
close pdf file before closing excel | Excel Programming | |||
Error closing Excel after running series of macros | Excel Programming | |||
Disable Macros --> Close File | Setting up and Configuration of Excel |