| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto shutdown for inactivity in Excel
I found this from an earlier post. Where and how do I put this in to make it
work? That code does not look for inactivity. It closes the file after 30 minutes regardless... Here is a slight modification of that code. It will pop up a timed message box every (currently set to 20 seconds for degbugging but you can change it to 30 minutes). The message box will stay up for 2 seconds. If you hit yes then the 20 second (30 Minute) clock will start again. If not then it saves and closes the file... Not you need to reference "Windows Script Host Object Model" (in the VBE Tools - References - "Windows Script Host Object Model"). This code sould be placed in a standard code module. Sub Auto_Open() Application.OnTime Now() + TimeValue("00:00:20"), "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?", secondstowait:=2, _ Title:="Active", Type:=vbYesNo) If Res = vbYes Then Application.OnTime Now() + TimeValue("00:00:20"), "CloseMe" Else ThisWorkbook.Save ThisWorkbook.Close End If End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Auto shutdown for inactivity in Excel
in a standard code module
(in the app develeopment environment. INSERT MODULE) the Auto_Open name means that this will run when the workbook opens "jtfalk" wrote: I found this from an earlier post. Where and how do I put this in to make it work? That code does not look for inactivity. It closes the file after 30 minutes regardless... Here is a slight modification of that code. It will pop up a timed message box every (currently set to 20 seconds for degbugging but you can change it to 30 minutes). The message box will stay up for 2 seconds. If you hit yes then the 20 second (30 Minute) clock will start again. If not then it saves and closes the file... Not you need to reference "Windows Script Host Object Model" (in the VBE Tools - References - "Windows Script Host Object Model"). This code sould be placed in a standard code module. Sub Auto_Open() Application.OnTime Now() + TimeValue("00:00:20"), "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?", secondstowait:=2, _ Title:="Active", Type:=vbYesNo) If Res = vbYes Then Application.OnTime Now() + TimeValue("00:00:20"), "CloseMe" Else ThisWorkbook.Save ThisWorkbook.Close End If End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Auto shutdown for inactivity in Excel
I went into tools-macros-visual basic
I clicked on ThisWorkbook and added the top 3 lines. I then added a module and pasted the other in. How do I get it to run? I saved it - closed it- and opened it again and it does nothing. "Patrick Molloy" wrote: in a standard code module (in the app develeopment environment. INSERT MODULE) the Auto_Open name means that this will run when the workbook opens "jtfalk" wrote: I found this from an earlier post. Where and how do I put this in to make it work? That code does not look for inactivity. It closes the file after 30 minutes regardless... Here is a slight modification of that code. It will pop up a timed message box every (currently set to 20 seconds for degbugging but you can change it to 30 minutes). The message box will stay up for 2 seconds. If you hit yes then the 20 second (30 Minute) clock will start again. If not then it saves and closes the file... Not you need to reference "Windows Script Host Object Model" (in the VBE Tools - References - "Windows Script Host Object Model"). This code sould be placed in a standard code module. Sub Auto_Open() Application.OnTime Now() + TimeValue("00:00:20"), "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?", secondstowait:=2, _ Title:="Active", Type:=vbYesNo) If Res = vbYes Then Application.OnTime Now() + TimeValue("00:00:20"), "CloseMe" Else ThisWorkbook.Save ThisWorkbook.Close End If End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Auto shutdown for inactivity in Excel
I get a
Compile error, User defined type not defined Dim SH As IWshRuntimeLibrary.WshShell (with this highlighted) 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?", secondstowait:=2, _ Title:="Active", Type:=vbYesNo) If Res = vbYes Then Application.OnTime Now() + TimeValue("00:00:20"), "CloseMe" Else ThisWorkbook.Save ThisWorkbook.Close End If End Sub "Patrick Molloy" wrote: in a standard code module (in the app develeopment environment. INSERT MODULE) the Auto_Open name means that this will run when the workbook opens "jtfalk" wrote: I found this from an earlier post. Where and how do I put this in to make it work? That code does not look for inactivity. It closes the file after 30 minutes regardless... Here is a slight modification of that code. It will pop up a timed message box every (currently set to 20 seconds for degbugging but you can change it to 30 minutes). The message box will stay up for 2 seconds. If you hit yes then the 20 second (30 Minute) clock will start again. If not then it saves and closes the file... Not you need to reference "Windows Script Host Object Model" (in the VBE Tools - References - "Windows Script Host Object Model"). This code sould be placed in a standard code module. Sub Auto_Open() Application.OnTime Now() + TimeValue("00:00:20"), "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?", secondstowait:=2, _ Title:="Active", Type:=vbYesNo) If Res = vbYes Then Application.OnTime Now() + TimeValue("00:00:20"), "CloseMe" Else ThisWorkbook.Save ThisWorkbook.Close End If End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Auto shutdown for inactivity in Excel
not thisworkbook, as I wrote add a new code module
ALT+F11 from the excel workbook to open the editor then INTERT ( a menu item ) then "MODULE" ( NOT "CLASS MODULE") "jtfalk" wrote: I went into tools-macros-visual basic I clicked on ThisWorkbook and added the top 3 lines. I then added a module and pasted the other in. How do I get it to run? I saved it - closed it- and opened it again and it does nothing. "Patrick Molloy" wrote: in a standard code module (in the app develeopment environment. INSERT MODULE) the Auto_Open name means that this will run when the workbook opens "jtfalk" wrote: I found this from an earlier post. Where and how do I put this in to make it work? That code does not look for inactivity. It closes the file after 30 minutes regardless... Here is a slight modification of that code. It will pop up a timed message box every (currently set to 20 seconds for degbugging but you can change it to 30 minutes). The message box will stay up for 2 seconds. If you hit yes then the 20 second (30 Minute) clock will start again. If not then it saves and closes the file... Not you need to reference "Windows Script Host Object Model" (in the VBE Tools - References - "Windows Script Host Object Model"). This code sould be placed in a standard code module. Sub Auto_Open() Application.OnTime Now() + TimeValue("00:00:20"), "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?", secondstowait:=2, _ Title:="Active", Type:=vbYesNo) If Res = vbYes Then Application.OnTime Now() + TimeValue("00:00:20"), "CloseMe" Else ThisWorkbook.Save ThisWorkbook.Close End If End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Auto shutdown for inactivity in Excel
and also you will need to set a reference to the Widows Scriot Host Model as
described in the code that you posted. "Patrick Molloy" wrote: not thisworkbook, as I wrote add a new code module ALT+F11 from the excel workbook to open the editor then INTERT ( a menu item ) then "MODULE" ( NOT "CLASS MODULE") "jtfalk" wrote: I went into tools-macros-visual basic I clicked on ThisWorkbook and added the top 3 lines. I then added a module and pasted the other in. How do I get it to run? I saved it - closed it- and opened it again and it does nothing. "Patrick Molloy" wrote: in a standard code module (in the app develeopment environment. INSERT MODULE) the Auto_Open name means that this will run when the workbook opens "jtfalk" wrote: I found this from an earlier post. Where and how do I put this in to make it work? That code does not look for inactivity. It closes the file after 30 minutes regardless... Here is a slight modification of that code. It will pop up a timed message box every (currently set to 20 seconds for degbugging but you can change it to 30 minutes). The message box will stay up for 2 seconds. If you hit yes then the 20 second (30 Minute) clock will start again. If not then it saves and closes the file... Not you need to reference "Windows Script Host Object Model" (in the VBE Tools - References - "Windows Script Host Object Model"). This code sould be placed in a standard code module. Sub Auto_Open() Application.OnTime Now() + TimeValue("00:00:20"), "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?", secondstowait:=2, _ Title:="Active", Type:=vbYesNo) If Res = vbYes Then Application.OnTime Now() + TimeValue("00:00:20"), "CloseMe" Else ThisWorkbook.Save ThisWorkbook.Close End If End Sub |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Auto shutdown for inactivity in Excel
I put all the code in the newly made module (not class) and ran it - it
errored out on that same line Dim SH As IWshRuntimeLibrary.WshShell The same thing happened when I put this in the ThisWorkbook: Sub Auto_Open() Application.OnTime Now() + TimeValue("00:00:20"), "CloseMe" End Sub And this in the newly made module: '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?", secondstowait:=2, _ Title:="Active", Type:=vbYesNo) If Res = vbYes Then Application.OnTime Now() + TimeValue("00:00:20"), "CloseMe" Else ThisWorkbook.Save ThisWorkbook.Close End If End Sub What is Windows Script Host Object Model? is this something that I have to do and that is why it keeps erroring out? It runs auctomatically but errors out is that Dim SH As IWshRuntimeLibrary.WshShell correct or do I have to add something in to get that to run? Thanks "Patrick Molloy" wrote: not thisworkbook, as I wrote add a new code module ALT+F11 from the excel workbook to open the editor then INTERT ( a menu item ) then "MODULE" ( NOT "CLASS MODULE") "jtfalk" wrote: I went into tools-macros-visual basic I clicked on ThisWorkbook and added the top 3 lines. I then added a module and pasted the other in. How do I get it to run? I saved it - closed it- and opened it again and it does nothing. "Patrick Molloy" wrote: in a standard code module (in the app develeopment environment. INSERT MODULE) the Auto_Open name means that this will run when the workbook opens "jtfalk" wrote: I found this from an earlier post. Where and how do I put this in to make it work? That code does not look for inactivity. It closes the file after 30 minutes regardless... Here is a slight modification of that code. It will pop up a timed message box every (currently set to 20 seconds for degbugging but you can change it to 30 minutes). The message box will stay up for 2 seconds. If you hit yes then the 20 second (30 Minute) clock will start again. If not then it saves and closes the file... Not you need to reference "Windows Script Host Object Model" (in the VBE Tools - References - "Windows Script Host Object Model"). This code sould be placed in a standard code module. Sub Auto_Open() Application.OnTime Now() + TimeValue("00:00:20"), "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?", secondstowait:=2, _ Title:="Active", Type:=vbYesNo) If Res = vbYes Then Application.OnTime Now() + TimeValue("00:00:20"), "CloseMe" Else ThisWorkbook.Save ThisWorkbook.Close End If End Sub |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Auto shutdown for inactivity in Excel
How do I do that?
"Patrick Molloy" wrote: and also you will need to set a reference to the Widows Scriot Host Model as described in the code that you posted. "Patrick Molloy" wrote: not thisworkbook, as I wrote add a new code module ALT+F11 from the excel workbook to open the editor then INTERT ( a menu item ) then "MODULE" ( NOT "CLASS MODULE") "jtfalk" wrote: I went into tools-macros-visual basic I clicked on ThisWorkbook and added the top 3 lines. I then added a module and pasted the other in. How do I get it to run? I saved it - closed it- and opened it again and it does nothing. "Patrick Molloy" wrote: in a standard code module (in the app develeopment environment. INSERT MODULE) the Auto_Open name means that this will run when the workbook opens "jtfalk" wrote: I found this from an earlier post. Where and how do I put this in to make it work? That code does not look for inactivity. It closes the file after 30 minutes regardless... Here is a slight modification of that code. It will pop up a timed message box every (currently set to 20 seconds for degbugging but you can change it to 30 minutes). The message box will stay up for 2 seconds. If you hit yes then the 20 second (30 Minute) clock will start again. If not then it saves and closes the file... Not you need to reference "Windows Script Host Object Model" (in the VBE Tools - References - "Windows Script Host Object Model"). This code sould be placed in a standard code module. Sub Auto_Open() Application.OnTime Now() + TimeValue("00:00:20"), "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?", secondstowait:=2, _ Title:="Active", Type:=vbYesNo) If Res = vbYes Then Application.OnTime Now() + TimeValue("00:00:20"), "CloseMe" Else ThisWorkbook.Save ThisWorkbook.Close End If End Sub |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Auto shutdown for inactivity in Excel
Try:
Option Explicit Public Sub CloseMe() Dim SH As Object 'IWshRuntimeLibrary.WshShell Dim Res As Long 'Set SH = New IWshRuntimeLibrary.WshShell Set SH = CreateObject("WScript.Shell") Res = SH.Popup(Text:="Are you still there?", secondstowait:=2, _ Title:="Active", Type:=vbYesNo) If Res = vbYes Then Application.OnTime Now() + TimeValue("00:00:20"), "CloseMe" Else ThisWorkbook.Save ThisWorkbook.Close End If End Sub And you'll avoid having to use a reference. Tools|References Scrolling down the list looking for: Windows Script Host Object Model and checking it. ====== Just some things to be aware of... Some versions of windows won't close that popup. You'll want to test your code on all versions that you support. And I'm surprised that any developer would do this. I would have no idea if that workbook should be saved or closed without saving. If the code closes it without saving, then I could have destroyed hours of changes that the user did. If the code closes it and saves it, then I could be destroying data by saving changes that shouldn't have been saved (maybe the user made a significant change to play a what-if game or maybe the user just made a horrible mistake). Either way, I'm don't understand how a developer can know and why they'd make either assumption. jtfalk wrote: I get a Compile error, User defined type not defined Dim SH As IWshRuntimeLibrary.WshShell (with this highlighted) 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?", secondstowait:=2, _ Title:="Active", Type:=vbYesNo) If Res = vbYes Then Application.OnTime Now() + TimeValue("00:00:20"), "CloseMe" Else ThisWorkbook.Save ThisWorkbook.Close End If End Sub "Patrick Molloy" wrote: in a standard code module (in the app develeopment environment. INSERT MODULE) the Auto_Open name means that this will run when the workbook opens "jtfalk" wrote: I found this from an earlier post. Where and how do I put this in to make it work? That code does not look for inactivity. It closes the file after 30 minutes regardless... Here is a slight modification of that code. It will pop up a timed message box every (currently set to 20 seconds for degbugging but you can change it to 30 minutes). The message box will stay up for 2 seconds. If you hit yes then the 20 second (30 Minute) clock will start again. If not then it saves and closes the file... Not you need to reference "Windows Script Host Object Model" (in the VBE Tools - References - "Windows Script Host Object Model"). This code sould be placed in a standard code module. Sub Auto_Open() Application.OnTime Now() + TimeValue("00:00:20"), "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?", secondstowait:=2, _ Title:="Active", Type:=vbYesNo) If Res = vbYes Then Application.OnTime Now() + TimeValue("00:00:20"), "CloseMe" Else ThisWorkbook.Save ThisWorkbook.Close End If End Sub -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Auto shutdown for inactivity in Excel
I found the script and checked it and it works great. Thanks for your help
"Dave Peterson" wrote: Try: Option Explicit Public Sub CloseMe() Dim SH As Object 'IWshRuntimeLibrary.WshShell Dim Res As Long 'Set SH = New IWshRuntimeLibrary.WshShell Set SH = CreateObject("WScript.Shell") Res = SH.Popup(Text:="Are you still there?", secondstowait:=2, _ Title:="Active", Type:=vbYesNo) If Res = vbYes Then Application.OnTime Now() + TimeValue("00:00:20"), "CloseMe" Else ThisWorkbook.Save ThisWorkbook.Close End If End Sub And you'll avoid having to use a reference. Tools|References Scrolling down the list looking for: Windows Script Host Object Model and checking it. ====== Just some things to be aware of... Some versions of windows won't close that popup. You'll want to test your code on all versions that you support. And I'm surprised that any developer would do this. I would have no idea if that workbook should be saved or closed without saving. If the code closes it without saving, then I could have destroyed hours of changes that the user did. If the code closes it and saves it, then I could be destroying data by saving changes that shouldn't have been saved (maybe the user made a significant change to play a what-if game or maybe the user just made a horrible mistake). Either way, I'm don't understand how a developer can know and why they'd make either assumption. jtfalk wrote: I get a Compile error, User defined type not defined Dim SH As IWshRuntimeLibrary.WshShell (with this highlighted) 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?", secondstowait:=2, _ Title:="Active", Type:=vbYesNo) If Res = vbYes Then Application.OnTime Now() + TimeValue("00:00:20"), "CloseMe" Else ThisWorkbook.Save ThisWorkbook.Close End If End Sub "Patrick Molloy" wrote: in a standard code module (in the app develeopment environment. INSERT MODULE) the Auto_Open name means that this will run when the workbook opens "jtfalk" wrote: I found this from an earlier post. Where and how do I put this in to make it work? That code does not look for inactivity. It closes the file after 30 minutes regardless... Here is a slight modification of that code. It will pop up a timed message box every (currently set to 20 seconds for degbugging but you can change it to 30 minutes). The message box will stay up for 2 seconds. If you hit yes then the 20 second (30 Minute) clock will start again. If not then it saves and closes the file... Not you need to reference "Windows Script Host Object Model" (in the VBE Tools - References - "Windows Script Host Object Model"). This code sould be placed in a standard code module. Sub Auto_Open() Application.OnTime Now() + TimeValue("00:00:20"), "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?", secondstowait:=2, _ Title:="Active", Type:=vbYesNo) If Res = vbYes Then Application.OnTime Now() + TimeValue("00:00:20"), "CloseMe" Else ThisWorkbook.Save ThisWorkbook.Close End If End Sub -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Autoclose excel file after x minutes of inactivity | Excel Programming | |||
| Auto Shutdown | Excel Programming | |||
| Auto saving & closing a file after inactivity | Excel Programming | |||
| excel shutdown | Excel Discussion (Misc queries) | |||
| Excel, VB.Net and shutdown | Excel Programming | |||
Linear Mode
