| Home | Forums | Reviews | Articles | Register |
 |
| Thread Tools | Rate Thread |
|
Guest
Posts: n/a
|
|
|
|
||
|
|
| |
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
maybe because you're forcing it to run & 20 minutes hasn't elapsed yet?
or maybe not - just a thought.  susan "Jock" <(E-Mail Removed)> wrote in message news:072A1815-3857-40B0-9A5E-(E-Mail Removed)... > Bernie, > Copied & pasted your code. > However, I get an error message: > Run-time error '1004' > Method 'on time' of object '_Application' failed > > What have I done wrong here? > > -- > tia > > Jock > > > "Bernie Deitrick" wrote: > >> Jock, >> >> Put this code into a regular codemodule: >> >> Public RunTime As Date >> >> Sub SaveAndCloseMe() >> Application.DisplayAlerts = False >> ThisWorkbook.Close True >> Application.DisplayAlerts = True >> End Sub >> >> >> And put this code into the ThisWorkbook object's codemodule: >> >> Private Sub Workbook_Open() >> RunTime = Now() + TimeValue("00:20:00") >> Application.OnTime RunTime, "SaveAndCloseMe" >> End Sub >> >> Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As >> Range) >> Application.OnTime RunTime, "SaveAndCloseMe", , False >> RunTime = Now() + TimeValue("00:20:00") >> Application.OnTime RunTime, "SaveAndCloseMe" >> End Sub >> >> It will save and close the workbook after it hasn't been changed for 20 >> minutes. >> >> HTH, >> Bernie >> MS Excel MVP >> >> >> "Jock" <(E-Mail Removed)> wrote in message >> news:E8D50B92-D174-4680-85AC-(E-Mail Removed)... >> > Quite often, somone in our office has a certain file open to which >> > others >> > need access. The person who has it open hasn't made changes for, say 20 >> > mins. >> > Is it possible to have the spreadsheet automatically save and close >> > after a >> > specified time period has elapsed? >> > -- >> > tia >> > >> > Jock >> >> >> |
|
|
|
|
|||
|
Guest
Posts: n/a
|
If only it was that easy.
All I did (after entering the code) was change the value in a cell and tabbed out of it. The error message appears immediately. -- tia Jock "Susan" wrote: > maybe because you're forcing it to run & 20 minutes hasn't elapsed yet? > or maybe not - just a thought. > > susan > > > "Jock" <(E-Mail Removed)> wrote in message > news:072A1815-3857-40B0-9A5E-(E-Mail Removed)... > > Bernie, > > Copied & pasted your code. > > However, I get an error message: > > Run-time error '1004' > > Method 'on time' of object '_Application' failed > > > > What have I done wrong here? > > > > -- > > tia > > > > Jock > > > > > > "Bernie Deitrick" wrote: > > > >> Jock, > >> > >> Put this code into a regular codemodule: > >> > >> Public RunTime As Date > >> > >> Sub SaveAndCloseMe() > >> Application.DisplayAlerts = False > >> ThisWorkbook.Close True > >> Application.DisplayAlerts = True > >> End Sub > >> > >> > >> And put this code into the ThisWorkbook object's codemodule: > >> > >> Private Sub Workbook_Open() > >> RunTime = Now() + TimeValue("00:20:00") > >> Application.OnTime RunTime, "SaveAndCloseMe" > >> End Sub > >> > >> Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As > >> Range) > >> Application.OnTime RunTime, "SaveAndCloseMe", , False > >> RunTime = Now() + TimeValue("00:20:00") > >> Application.OnTime RunTime, "SaveAndCloseMe" > >> End Sub > >> > >> It will save and close the workbook after it hasn't been changed for 20 > >> minutes. > >> > >> HTH, > >> Bernie > >> MS Excel MVP > >> > >> > >> "Jock" <(E-Mail Removed)> wrote in message > >> news:E8D50B92-D174-4680-85AC-(E-Mail Removed)... > >> > Quite often, somone in our office has a certain file open to which > >> > others > >> > need access. The person who has it open hasn't made changes for, say 20 > >> > mins. > >> > Is it possible to have the spreadsheet automatically save and close > >> > after a > >> > specified time period has elapsed? > >> > -- > >> > tia > >> > > >> > Jock > >> > >> > >> > > > |
|
|
|
|
|||
|
Guest
Posts: n/a
|
Jock,
You got that message because there was no ontime event to cancel. I assumed that you would first save the workbook and your users would re-open it prior to making any changes. Either run this macro to initialize the ontime method: Sub StartOnTime() RunTime = Now() + TimeValue("00:20:00") Application.OnTime RunTime, "SaveAndCloseMe" End Sub or add On Error Resume Next to the sheet change event: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) On Error Resume Next Application.OnTime RunTime, "SaveAndCloseMe", , False RunTime = Now() + TimeValue("00:20:00") Application.OnTime RunTime, "SaveAndCloseMe" End Sub Sorry about that, Bernie MS Excel MVP "Jock" <(E-Mail Removed)> wrote in message news:89C696BF-CC56-4853-AB72-(E-Mail Removed)... > If only it was that easy. > All I did (after entering the code) was change the value in a cell and > tabbed out of it. The error message appears immediately. > -- > tia > > Jock > > > "Susan" wrote: > >> maybe because you're forcing it to run & 20 minutes hasn't elapsed yet? >> or maybe not - just a thought. >> >> susan >> >> >> "Jock" <(E-Mail Removed)> wrote in message >> news:072A1815-3857-40B0-9A5E-(E-Mail Removed)... >> > Bernie, >> > Copied & pasted your code. >> > However, I get an error message: >> > Run-time error '1004' >> > Method 'on time' of object '_Application' failed >> > >> > What have I done wrong here? >> > >> > -- >> > tia >> > >> > Jock >> > >> > >> > "Bernie Deitrick" wrote: >> > >> >> Jock, >> >> >> >> Put this code into a regular codemodule: >> >> >> >> Public RunTime As Date >> >> >> >> Sub SaveAndCloseMe() >> >> Application.DisplayAlerts = False >> >> ThisWorkbook.Close True >> >> Application.DisplayAlerts = True >> >> End Sub >> >> >> >> >> >> And put this code into the ThisWorkbook object's codemodule: >> >> >> >> Private Sub Workbook_Open() >> >> RunTime = Now() + TimeValue("00:20:00") >> >> Application.OnTime RunTime, "SaveAndCloseMe" >> >> End Sub >> >> >> >> Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As >> >> Range) >> >> Application.OnTime RunTime, "SaveAndCloseMe", , False >> >> RunTime = Now() + TimeValue("00:20:00") >> >> Application.OnTime RunTime, "SaveAndCloseMe" >> >> End Sub >> >> >> >> It will save and close the workbook after it hasn't been changed for 20 >> >> minutes. >> >> >> >> HTH, >> >> Bernie >> >> MS Excel MVP >> >> >> >> >> >> "Jock" <(E-Mail Removed)> wrote in message >> >> news:E8D50B92-D174-4680-85AC-(E-Mail Removed)... >> >> > Quite often, somone in our office has a certain file open to which >> >> > others >> >> > need access. The person who has it open hasn't made changes for, say 20 >> >> > mins. >> >> > Is it possible to have the spreadsheet automatically save and close >> >> > after a >> >> > specified time period has elapsed? >> >> > -- >> >> > tia >> >> > >> >> > Jock >> >> >> >> >> >> >> >> >> |
|
|
|
|
|||
|
Guest
Posts: n/a
|
Thanks Bernie, works a treat.
I have noticed however, if the user enters data but doesn't tab out or press <enter> to move to another cell, the workbook doesn't close. -- tia Jock "Bernie Deitrick" wrote: > Jock, > > You got that message because there was no ontime event to cancel. I assumed that you would first > save the workbook and your users would re-open it prior to making any changes. > > Either run this macro to initialize the ontime method: > > Sub StartOnTime() > RunTime = Now() + TimeValue("00:20:00") > Application.OnTime RunTime, "SaveAndCloseMe" > End Sub > > or add > > On Error Resume Next > > to the sheet change event: > > Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) > > On Error Resume Next > Application.OnTime RunTime, "SaveAndCloseMe", , False > RunTime = Now() + TimeValue("00:20:00") > Application.OnTime RunTime, "SaveAndCloseMe" > End Sub > > > Sorry about that, > Bernie > MS Excel MVP > > > "Jock" <(E-Mail Removed)> wrote in message > news:89C696BF-CC56-4853-AB72-(E-Mail Removed)... > > If only it was that easy. > > All I did (after entering the code) was change the value in a cell and > > tabbed out of it. The error message appears immediately. > > -- > > tia > > > > Jock > > > > > > "Susan" wrote: > > > >> maybe because you're forcing it to run & 20 minutes hasn't elapsed yet? > >> or maybe not - just a thought. > >> > >> susan > >> > >> > >> "Jock" <(E-Mail Removed)> wrote in message > >> news:072A1815-3857-40B0-9A5E-(E-Mail Removed)... > >> > Bernie, > >> > Copied & pasted your code. > >> > However, I get an error message: > >> > Run-time error '1004' > >> > Method 'on time' of object '_Application' failed > >> > > >> > What have I done wrong here? > >> > > >> > -- > >> > tia > >> > > >> > Jock > >> > > >> > > >> > "Bernie Deitrick" wrote: > >> > > >> >> Jock, > >> >> > >> >> Put this code into a regular codemodule: > >> >> > >> >> Public RunTime As Date > >> >> > >> >> Sub SaveAndCloseMe() > >> >> Application.DisplayAlerts = False > >> >> ThisWorkbook.Close True > >> >> Application.DisplayAlerts = True > >> >> End Sub > >> >> > >> >> > >> >> And put this code into the ThisWorkbook object's codemodule: > >> >> > >> >> Private Sub Workbook_Open() > >> >> RunTime = Now() + TimeValue("00:20:00") > >> >> Application.OnTime RunTime, "SaveAndCloseMe" > >> >> End Sub > >> >> > >> >> Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As > >> >> Range) > >> >> Application.OnTime RunTime, "SaveAndCloseMe", , False > >> >> RunTime = Now() + TimeValue("00:20:00") > >> >> Application.OnTime RunTime, "SaveAndCloseMe" > >> >> End Sub > >> >> > >> >> It will save and close the workbook after it hasn't been changed for 20 > >> >> minutes. > >> >> > >> >> HTH, > >> >> Bernie > >> >> MS Excel MVP > >> >> > >> >> > >> >> "Jock" <(E-Mail Removed)> wrote in message > >> >> news:E8D50B92-D174-4680-85AC-(E-Mail Removed)... > >> >> > Quite often, somone in our office has a certain file open to which > >> >> > others > >> >> > need access. The person who has it open hasn't made changes for, say 20 > >> >> > mins. > >> >> > Is it possible to have the spreadsheet automatically save and close > >> >> > after a > >> >> > specified time period has elapsed? > >> >> > -- > >> >> > tia > >> >> > > >> >> > Jock > >> >> > >> >> > >> >> > >> > >> > >> > > > |
|
|
|
|
|||
|
Guest
Posts: n/a
|
Jock,
VBA code is disabled when Excel is in Edit mode, so there is no recourse except user training. HTH, Bernie MS Excel MVP "Jock" <(E-Mail Removed)> wrote in message news:17523470-9813-40B8-AF51-(E-Mail Removed)... > Thanks Bernie, works a treat. > I have noticed however, if the user enters data but doesn't tab out or press > <enter> to move to another cell, the workbook doesn't close. > -- > tia > > Jock > > > "Bernie Deitrick" wrote: > >> Jock, >> >> You got that message because there was no ontime event to cancel. I assumed that you would first >> save the workbook and your users would re-open it prior to making any changes. >> >> Either run this macro to initialize the ontime method: >> >> Sub StartOnTime() >> RunTime = Now() + TimeValue("00:20:00") >> Application.OnTime RunTime, "SaveAndCloseMe" >> End Sub >> >> or add >> >> On Error Resume Next >> >> to the sheet change event: >> >> Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) >> >> On Error Resume Next >> Application.OnTime RunTime, "SaveAndCloseMe", , False >> RunTime = Now() + TimeValue("00:20:00") >> Application.OnTime RunTime, "SaveAndCloseMe" >> End Sub >> >> >> Sorry about that, >> Bernie >> MS Excel MVP >> >> >> "Jock" <(E-Mail Removed)> wrote in message >> news:89C696BF-CC56-4853-AB72-(E-Mail Removed)... >> > If only it was that easy. >> > All I did (after entering the code) was change the value in a cell and >> > tabbed out of it. The error message appears immediately. >> > -- >> > tia >> > >> > Jock >> > >> > >> > "Susan" wrote: >> > >> >> maybe because you're forcing it to run & 20 minutes hasn't elapsed yet? >> >> or maybe not - just a thought. >> >> >> >> susan >> >> >> >> >> >> "Jock" <(E-Mail Removed)> wrote in message >> >> news:072A1815-3857-40B0-9A5E-(E-Mail Removed)... >> >> > Bernie, >> >> > Copied & pasted your code. >> >> > However, I get an error message: >> >> > Run-time error '1004' >> >> > Method 'on time' of object '_Application' failed >> >> > >> >> > What have I done wrong here? >> >> > >> >> > -- >> >> > tia >> >> > >> >> > Jock >> >> > >> >> > >> >> > "Bernie Deitrick" wrote: >> >> > >> >> >> Jock, >> >> >> >> >> >> Put this code into a regular codemodule: >> >> >> >> >> >> Public RunTime As Date >> >> >> >> >> >> Sub SaveAndCloseMe() >> >> >> Application.DisplayAlerts = False >> >> >> ThisWorkbook.Close True >> >> >> Application.DisplayAlerts = True >> >> >> End Sub >> >> >> >> >> >> >> >> >> And put this code into the ThisWorkbook object's codemodule: >> >> >> >> >> >> Private Sub Workbook_Open() >> >> >> RunTime = Now() + TimeValue("00:20:00") >> >> >> Application.OnTime RunTime, "SaveAndCloseMe" >> >> >> End Sub >> >> >> >> >> >> Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As >> >> >> Range) >> >> >> Application.OnTime RunTime, "SaveAndCloseMe", , False >> >> >> RunTime = Now() + TimeValue("00:20:00") >> >> >> Application.OnTime RunTime, "SaveAndCloseMe" >> >> >> End Sub >> >> >> >> >> >> It will save and close the workbook after it hasn't been changed for 20 >> >> >> minutes. >> >> >> >> >> >> HTH, >> >> >> Bernie >> >> >> MS Excel MVP >> >> >> >> >> >> >> >> >> "Jock" <(E-Mail Removed)> wrote in message >> >> >> news:E8D50B92-D174-4680-85AC-(E-Mail Removed)... >> >> >> > Quite often, somone in our office has a certain file open to which >> >> >> > others >> >> >> > need access. The person who has it open hasn't made changes for, say 20 >> >> >> > mins. >> >> >> > Is it possible to have the spreadsheet automatically save and close >> >> >> > after a >> >> >> > specified time period has elapsed? >> >> >> > -- >> >> >> > tia >> >> >> > >> >> >> > Jock >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> |
|
|
|
|
|||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
And then, how can i check the edit cell mode by using excel.application object?
"Bernie Deitrick" wrote: > Jock, > > VBA code is disabled when Excel is in Edit mode, so there is no recourse except user training. > > HTH, > Bernie > MS Excel MVP > > > "Jock" <(E-Mail Removed)> wrote in message > news:17523470-9813-40B8-AF51-(E-Mail Removed)... > > Thanks Bernie, works a treat. > > I have noticed however, if the user enters data but doesn't tab out or press > > <enter> to move to another cell, the workbook doesn't close. > > -- > > tia > > > > Jock > > > > > > "Bernie Deitrick" wrote: > > > >> Jock, > >> > >> You got that message because there was no ontime event to cancel. I assumed that you would first > >> save the workbook and your users would re-open it prior to making any changes. > >> > >> Either run this macro to initialize the ontime method: > >> > >> Sub StartOnTime() > >> RunTime = Now() + TimeValue("00:20:00") > >> Application.OnTime RunTime, "SaveAndCloseMe" > >> End Sub > >> > >> or add > >> > >> On Error Resume Next > >> > >> to the sheet change event: > >> > >> Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) > >> > >> On Error Resume Next > >> Application.OnTime RunTime, "SaveAndCloseMe", , False > >> RunTime = Now() + TimeValue("00:20:00") > >> Application.OnTime RunTime, "SaveAndCloseMe" > >> End Sub > >> > >> > >> Sorry about that, > >> Bernie > >> MS Excel MVP > >> > >> > >> "Jock" <(E-Mail Removed)> wrote in message > >> news:89C696BF-CC56-4853-AB72-(E-Mail Removed)... > >> > If only it was that easy. > >> > All I did (after entering the code) was change the value in a cell and > >> > tabbed out of it. The error message appears immediately. > >> > -- > >> > tia > >> > > >> > Jock > >> > > >> > > >> > "Susan" wrote: > >> > > >> >> maybe because you're forcing it to run & 20 minutes hasn't elapsed yet? > >> >> or maybe not - just a thought. > >> >> > >> >> susan > >> >> > >> >> > >> >> "Jock" <(E-Mail Removed)> wrote in message > >> >> news:072A1815-3857-40B0-9A5E-(E-Mail Removed)... > >> >> > Bernie, > >> >> > Copied & pasted your code. > >> >> > However, I get an error message: > >> >> > Run-time error '1004' > >> >> > Method 'on time' of object '_Application' failed > >> >> > > >> >> > What have I done wrong here? > >> >> > > >> >> > -- > >> >> > tia > >> >> > > >> >> > Jock > >> >> > > >> >> > > >> >> > "Bernie Deitrick" wrote: > >> >> > > >> >> >> Jock, > >> >> >> > >> >> >> Put this code into a regular codemodule: > >> >> >> > >> >> >> Public RunTime As Date > >> >> >> > >> >> >> Sub SaveAndCloseMe() > >> >> >> Application.DisplayAlerts = False > >> >> >> ThisWorkbook.Close True > >> >> >> Application.DisplayAlerts = True > >> >> >> End Sub > >> >> >> > >> >> >> > >> >> >> And put this code into the ThisWorkbook object's codemodule: > >> >> >> > >> >> >> Private Sub Workbook_Open() > >> >> >> RunTime = Now() + TimeValue("00:20:00") > >> >> >> Application.OnTime RunTime, "SaveAndCloseMe" > >> >> >> End Sub > >> >> >> > >> >> >> Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As > >> >> >> Range) > >> >> >> Application.OnTime RunTime, "SaveAndCloseMe", , False > >> >> >> RunTime = Now() + TimeValue("00:20:00") > >> >> >> Application.OnTime RunTime, "SaveAndCloseMe" > >> >> >> End Sub > >> >> >> > >> >> >> It will save and close the workbook after it hasn't been changed for 20 > >> >> >> minutes. > >> >> >> > >> >> >> HTH, > >> >> >> Bernie > >> >> >> MS Excel MVP > >> >> >> > >> >> >> > >> >> >> "Jock" <(E-Mail Removed)> wrote in message > >> >> >> news:E8D50B92-D174-4680-85AC-(E-Mail Removed)... > >> >> >> > Quite often, somone in our office has a certain file open to which > >> >> >> > others > >> >> >> > need access. The person who has it open hasn't made changes for, say 20 > >> >> >> > mins. > >> >> >> > Is it possible to have the spreadsheet automatically save and close > >> >> >> > after a > >> >> >> > specified time period has elapsed? > >> >> >> > -- > >> >> >> > tia > >> >> >> > > >> >> >> > Jock > >> >> >> > >> >> >> > >> >> >> > >> >> > >> >> > >> >> > >> > >> > >> > > > |
|
|
|
|
|||
|
|
| |
|
| Thread Tools | |
| Rate This Thread | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Automatic closing Excel | Just.in | Microsoft Excel Misc | 1 | 10th Jul 2008 01:01 AM |
| Creating an automatic 'closing', e.g. | djprius | Microsoft Word Document Management | 8 | 3rd Sep 2007 09:21 PM |
| Mirc's automatic closing | =?Utf-8?B?UEhJTlNJREU=?= | Windows Vista Installation | 1 | 28th Jan 2007 09:21 PM |
| Re: automatic closing of document | Ken Snell [MVP] | Microsoft Access Getting Started | 0 | 20th Aug 2004 10:34 PM |
| Automatic Disconnect when Closing IE | =?Utf-8?B?cm9zMg==?= | Windows XP General | 2 | 27th Dec 2003 07:56 PM |
