RE: Auto shutdown for inactivity in Excel

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

.

Relevant Pages

  • RXParse module v.91 (by robic0)
    ... # Unicode character reference ... sub original_content ... then call content handler with $content ...
    (comp.lang.perl.misc)
  • RE: Using excel to Determine if a File Exists in SharePoint
    ... Microsoft Scripting Runtime ... Is there a reference I need to add? ... Sub CheckIfexists() ... You just need to know your local sharepoint ...
    (microsoft.public.excel.programming)
  • Re: Emailing a Report
    ... make sure you have a reference to a Microsoft DAO Library ... The Sub LoopAgmtsSendEmail is highlighted by the Debugger. ... 'pSQL -- defines the recordset to open ... You must remember to enclose literal values in quotes and concatenate them; the final string must have commas too, ...
    (microsoft.public.access.modulesdaovba)
  • Re: Shared Method Problem With "Global" Storage
    ... a routine in Module1 which then calls code back in Form1 ... ButtonHasBeenClicked but the reference to ButtonHasBeenClicked ... instance member of a class from within a shared method or shared ... In a shared Sub, you can not access an instance field withouth ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Please help me clarify these byVal vs. byRef subtleties
    ... ByVal & ByRef Parameters are independent of Reference & Value Types. ... > I've got a class, DataHider, with one private string field (i.e., ... > Public Sub callObjectMethodPassedByVal(ByVal myObj As ...
    (microsoft.public.dotnet.languages.vb)