|
"Kicking Out" Inactive Users
I have a file that only allows one user at a time to have write/update
authority (first one in locks it up); there are five people with this modification authority. The problem is one of them opening it and then walking away for an extended period of time and thus the others cannot input their updates. I do not want to make it into a shared file. Similar to my company's auto password protected screen saver (kicks on after "x" number of inactive minutes), I would to have the file save itself and close if a write/update user hasn't navigated in the file via keyboard or mouse in say the last three minutes. Ideally, anyone who opened the file with €śread only€ť clearance (approximately a dozen users preset as Read Only) would not get €śkicked out€ť as they are not disrupting anyone. Any clues / insight? Regards, Mike |
"Kicking Out" Inactive Users
Mike, not sure where I got this code but this should get you started
this will auto-close the workbook after 20 seconds of inactivity '*************************************** 'put this in a standard module Dim DownTime As Date Sub SetTime() DownTime = Now + TimeValue("00:00:20") Application.OnTime DownTime, "ShutDown" End Sub Sub ShutDown() ThisWorkbook.Save ThisWorkbook.Close End Sub Sub Disable() On Error Resume Next Application.OnTime EarliestTime:=DownTime, Procedu="ShutDown", Schedule:=False End Sub '******************************************* '************************************ 'put this in thisworkbook Private Sub Workbook_Open() MsgBox "This workbook will auto-close after 20 seconds of inactivity" Call SetTime End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Call Disable End Sub Private Sub Workbook_SheetCalculate(ByVal Sh As Object) Call Disable Call SetTime End Sub Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range) Call Disable Call SetTime End Sub '***************************** -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Mike The Newb" wrote in message ... I have a file that only allows one user at a time to have write/update authority (first one in locks it up); there are five people with this modification authority. The problem is one of them opening it and then walking away for an extended period of time and thus the others cannot input their updates. I do not want to make it into a shared file. Similar to my company's auto password protected screen saver (kicks on after "x" number of inactive minutes), I would to have the file save itself and close if a write/update user hasn't navigated in the file via keyboard or mouse in say the last three minutes. Ideally, anyone who opened the file with "read only" clearance (approximately a dozen users preset as Read Only) would not get "kicked out" as they are not disrupting anyone. Any clues / insight? Regards, Mike |
"Kicking Out" Inactive Users
Paul - the code looks usable enough but I don't see a Standard Module. I can
find the Workbook but I only see Modules 1-10 in the VBE windows on the left side of the screen in one existing file and a newly created file shows no Modules listed at all. What is the Standard Module and where can I find it? Just a Newb like the nickname implies. Thank you for your insight and patience. Regards, Mike "Paul B" wrote: Mike, not sure where I got this code but this should get you started this will auto-close the workbook after 20 seconds of inactivity '*************************************** 'put this in a standard module Dim DownTime As Date Sub SetTime() DownTime = Now + TimeValue("00:00:20") Application.OnTime DownTime, "ShutDown" End Sub Sub ShutDown() ThisWorkbook.Save ThisWorkbook.Close End Sub Sub Disable() On Error Resume Next Application.OnTime EarliestTime:=DownTime, Procedu="ShutDown", Schedule:=False End Sub '******************************************* '************************************ 'put this in thisworkbook Private Sub Workbook_Open() MsgBox "This workbook will auto-close after 20 seconds of inactivity" Call SetTime End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Call Disable End Sub Private Sub Workbook_SheetCalculate(ByVal Sh As Object) Call Disable Call SetTime End Sub Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range) Call Disable Call SetTime End Sub '***************************** -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Mike The Newb" wrote in message ... I have a file that only allows one user at a time to have write/update authority (first one in locks it up); there are five people with this modification authority. The problem is one of them opening it and then walking away for an extended period of time and thus the others cannot input their updates. I do not want to make it into a shared file. Similar to my company's auto password protected screen saver (kicks on after "x" number of inactive minutes), I would to have the file save itself and close if a write/update user hasn't navigated in the file via keyboard or mouse in say the last three minutes. Ideally, anyone who opened the file with "read only" clearance (approximately a dozen users preset as Read Only) would not get "kicked out" as they are not disrupting anyone. Any clues / insight? Regards, Mike |
"Kicking Out" Inactive Users
Mike, click on your workbook on the left side, go up to insert ,module put
that code in there, you should see the thisworkbook module, you may also what to have a look here on getting started with macros http://www.mvps.org/dmcritchie/excel/getstarted.htm -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Mike The Newb" wrote in message ... Paul - the code looks usable enough but I don't see a Standard Module. I can find the Workbook but I only see Modules 1-10 in the VBE windows on the left side of the screen in one existing file and a newly created file shows no Modules listed at all. What is the Standard Module and where can I find it? Just a Newb like the nickname implies. Thank you for your insight and patience. Regards, Mike "Paul B" wrote: Mike, not sure where I got this code but this should get you started this will auto-close the workbook after 20 seconds of inactivity '*************************************** 'put this in a standard module Dim DownTime As Date Sub SetTime() DownTime = Now + TimeValue("00:00:20") Application.OnTime DownTime, "ShutDown" End Sub Sub ShutDown() ThisWorkbook.Save ThisWorkbook.Close End Sub Sub Disable() On Error Resume Next Application.OnTime EarliestTime:=DownTime, Procedu="ShutDown", Schedule:=False End Sub '******************************************* '************************************ 'put this in thisworkbook Private Sub Workbook_Open() MsgBox "This workbook will auto-close after 20 seconds of inactivity" Call SetTime End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Call Disable End Sub Private Sub Workbook_SheetCalculate(ByVal Sh As Object) Call Disable Call SetTime End Sub Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range) Call Disable Call SetTime End Sub '***************************** -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Mike The Newb" wrote in message ... I have a file that only allows one user at a time to have write/update authority (first one in locks it up); there are five people with this modification authority. The problem is one of them opening it and then walking away for an extended period of time and thus the others cannot input their updates. I do not want to make it into a shared file. Similar to my company's auto password protected screen saver (kicks on after "x" number of inactive minutes), I would to have the file save itself and close if a write/update user hasn't navigated in the file via keyboard or mouse in say the last three minutes. Ideally, anyone who opened the file with "read only" clearance (approximately a dozen users preset as Read Only) would not get "kicked out" as they are not disrupting anyone. Any clues / insight? Regards, Mike |
"Kicking Out" Inactive Users
Paul - almost there!
Paul - almost there. The code works great up until ThisWorkbook.Close and I get a "code interrupted" pop up; when I choose Debug it highlights the ThisWorkbook.Close and if I hit continue it runs its course and closes the file. Oddly, it does close itself, without any issues, if I am not in an Excel window watching it do its thing. Dim DownTime As Date Sub SetTime() DownTime = Now + TimeValue("00:00:20") Application.OnTime DownTime, "ShutDown" End Sub Sub ShutDown() ThisWorkbook.Save ThisWorkbook.Close End Sub Sub Disable() On Error Resume Next Application.OnTime EarliestTime:=DownTime, Procedu="ShutDown", Schedule:=False End Sub Regards, Mike "Paul B" wrote: Mike, click on your workbook on the left side, go up to insert ,module put that code in there, you should see the thisworkbook module, you may also what to have a look here on getting started with macros http://www.mvps.org/dmcritchie/excel/getstarted.htm -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Mike The Newb" wrote in message ... Paul - the code looks usable enough but I don't see a Standard Module. I can find the Workbook but I only see Modules 1-10 in the VBE windows on the left side of the screen in one existing file and a newly created file shows no Modules listed at all. What is the Standard Module and where can I find it? Just a Newb like the nickname implies. Thank you for your insight and patience. Regards, Mike "Paul B" wrote: Mike, not sure where I got this code but this should get you started this will auto-close the workbook after 20 seconds of inactivity '*************************************** 'put this in a standard module Dim DownTime As Date Sub SetTime() DownTime = Now + TimeValue("00:00:20") Application.OnTime DownTime, "ShutDown" End Sub Sub ShutDown() ThisWorkbook.Save ThisWorkbook.Close End Sub Sub Disable() On Error Resume Next Application.OnTime EarliestTime:=DownTime, Procedu="ShutDown", Schedule:=False End Sub '******************************************* '************************************ 'put this in thisworkbook Private Sub Workbook_Open() MsgBox "This workbook will auto-close after 20 seconds of inactivity" Call SetTime End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Call Disable End Sub Private Sub Workbook_SheetCalculate(ByVal Sh As Object) Call Disable Call SetTime End Sub Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range) Call Disable Call SetTime End Sub '***************************** -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Mike The Newb" wrote in message ... I have a file that only allows one user at a time to have write/update authority (first one in locks it up); there are five people with this modification authority. The problem is one of them opening it and then walking away for an extended period of time and thus the others cannot input their updates. I do not want to make it into a shared file. Similar to my company's auto password protected screen saver (kicks on after "x" number of inactive minutes), I would to have the file save itself and close if a write/update user hasn't navigated in the file via keyboard or mouse in say the last three minutes. Ideally, anyone who opened the file with "read only" clearance (approximately a dozen users preset as Read Only) would not get "kicked out" as they are not disrupting anyone. Any clues / insight? Regards, Mike |
"Kicking Out" Inactive Users
Mike, just tested and it works for me, try taking out the lines
ThisWorkbook.Save ThisWorkbook.Close and replacing with this ThisWorkbook.Close True -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Mike The Newb" wrote in message ... Paul - almost there! Paul - almost there. The code works great up until ThisWorkbook.Close and I get a "code interrupted" pop up; when I choose Debug it highlights the ThisWorkbook.Close and if I hit continue it runs its course and closes the file. Oddly, it does close itself, without any issues, if I am not in an Excel window watching it do its thing. Dim DownTime As Date Sub SetTime() DownTime = Now + TimeValue("00:00:20") Application.OnTime DownTime, "ShutDown" End Sub Sub ShutDown() ThisWorkbook.Save ThisWorkbook.Close End Sub Sub Disable() On Error Resume Next Application.OnTime EarliestTime:=DownTime, Procedu="ShutDown", Schedule:=False End Sub Regards, Mike "Paul B" wrote: Mike, click on your workbook on the left side, go up to insert ,module put that code in there, you should see the thisworkbook module, you may also what to have a look here on getting started with macros http://www.mvps.org/dmcritchie/excel/getstarted.htm -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Mike The Newb" wrote in message ... Paul - the code looks usable enough but I don't see a Standard Module. I can find the Workbook but I only see Modules 1-10 in the VBE windows on the left side of the screen in one existing file and a newly created file shows no Modules listed at all. What is the Standard Module and where can I find it? Just a Newb like the nickname implies. Thank you for your insight and patience. Regards, Mike "Paul B" wrote: Mike, not sure where I got this code but this should get you started this will auto-close the workbook after 20 seconds of inactivity '*************************************** 'put this in a standard module Dim DownTime As Date Sub SetTime() DownTime = Now + TimeValue("00:00:20") Application.OnTime DownTime, "ShutDown" End Sub Sub ShutDown() ThisWorkbook.Save ThisWorkbook.Close End Sub Sub Disable() On Error Resume Next Application.OnTime EarliestTime:=DownTime, Procedu="ShutDown", Schedule:=False End Sub '******************************************* '************************************ 'put this in thisworkbook Private Sub Workbook_Open() MsgBox "This workbook will auto-close after 20 seconds of inactivity" Call SetTime End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Call Disable End Sub Private Sub Workbook_SheetCalculate(ByVal Sh As Object) Call Disable Call SetTime End Sub Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range) Call Disable Call SetTime End Sub '***************************** -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Mike The Newb" wrote in message ... I have a file that only allows one user at a time to have write/update authority (first one in locks it up); there are five people with this modification authority. The problem is one of them opening it and then walking away for an extended period of time and thus the others cannot input their updates. I do not want to make it into a shared file. Similar to my company's auto password protected screen saver (kicks on after "x" number of inactive minutes), I would to have the file save itself and close if a write/update user hasn't navigated in the file via keyboard or mouse in say the last three minutes. Ideally, anyone who opened the file with "read only" clearance (approximately a dozen users preset as Read Only) would not get "kicked out" as they are not disrupting anyone. Any clues / insight? Regards, Mike |
"Kicking Out" Inactive Users
Paul - disregard - all is well today (not sure what changed). THANK YOU!
Regards, Mike "Mike The Newb" wrote: Paul - almost there! The code works great up until ThisWorkbook.Close and I get a "code interrupted" pop up; when I choose Debug it highlights the ThisWorkbook.Close and if I hit continue it runs its course and closes the file. Oddly, it does close itself, without any issues, if I am not in an Excel window watching it do its thing. Dim DownTime As Date Sub SetTime() DownTime = Now + TimeValue("00:00:20") Application.OnTime DownTime, "ShutDown" End Sub Sub ShutDown() ThisWorkbook.Save ThisWorkbook.Close End Sub Sub Disable() On Error Resume Next Application.OnTime EarliestTime:=DownTime, Procedu="ShutDown", Schedule:=False End Sub Regards, Mike "Paul B" wrote: Mike, click on your workbook on the left side, go up to insert ,module put that code in there, you should see the thisworkbook module, you may also what to have a look here on getting started with macros http://www.mvps.org/dmcritchie/excel/getstarted.htm -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Mike The Newb" wrote in message ... Paul - the code looks usable enough but I don't see a Standard Module. I can find the Workbook but I only see Modules 1-10 in the VBE windows on the left side of the screen in one existing file and a newly created file shows no Modules listed at all. What is the Standard Module and where can I find it? Just a Newb like the nickname implies. Thank you for your insight and patience. Regards, Mike "Paul B" wrote: Mike, not sure where I got this code but this should get you started this will auto-close the workbook after 20 seconds of inactivity '*************************************** 'put this in a standard module Dim DownTime As Date Sub SetTime() DownTime = Now + TimeValue("00:00:20") Application.OnTime DownTime, "ShutDown" End Sub Sub ShutDown() ThisWorkbook.Save ThisWorkbook.Close End Sub Sub Disable() On Error Resume Next Application.OnTime EarliestTime:=DownTime, Procedu="ShutDown", Schedule:=False End Sub '******************************************* '************************************ 'put this in thisworkbook Private Sub Workbook_Open() MsgBox "This workbook will auto-close after 20 seconds of inactivity" Call SetTime End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Call Disable End Sub Private Sub Workbook_SheetCalculate(ByVal Sh As Object) Call Disable Call SetTime End Sub Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range) Call Disable Call SetTime End Sub '***************************** -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Mike The Newb" wrote in message ... I have a file that only allows one user at a time to have write/update authority (first one in locks it up); there are five people with this modification authority. The problem is one of them opening it and then walking away for an extended period of time and thus the others cannot input their updates. I do not want to make it into a shared file. Similar to my company's auto password protected screen saver (kicks on after "x" number of inactive minutes), I would to have the file save itself and close if a write/update user hasn't navigated in the file via keyboard or mouse in say the last three minutes. Ideally, anyone who opened the file with "read only" clearance (approximately a dozen users preset as Read Only) would not get "kicked out" as they are not disrupting anyone. Any clues / insight? Regards, Mike |
"Kicking Out" Inactive Users
Sandy, are you talking about the message box on open? If I was using this
code I would take it out and just let everybody in the office know that the workbook will close after ??? time. The self closing message box would be a good way if you want it to show on open But there is another drawback, if the "bloody minded individual" walks away from the workbook, or wants to keep it open just for them, they could leave the workbook in edit, double click or F2 in a cell and the code will not work. :( -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Sandy Mann" wrote in message ... Paul, Excelent code whoever wrote it. I tried it at work in XL 2002, (we also have one bloody minded individual who leaves a common workbook open), one drawback is that if the bloody minded individual does not dismiss the messagebox then the code does not run. I tried using a Jim Rech Sub that Dave Peterson posted some time ago: Start of Dave's post *************************************** This was posted by Jim Rech to a similar request: If you have the Windows Scripting Host Obj model installed (WSHOM.OCX), and I believe it's part of Windows 98 among other things, you can run this: Sub SelfClosingMsgBox() CreateObject("WScript.Shell").Popup "Hello", 2, _ "This closes itself in 2 seconds" End Sub And if you wanted to be able to check to see if they hit ok or cancel: Sub SelfClosingMsgBox2() Dim Resp As Long Resp = CreateObject("WScript.Shell").Popup("Hello", 2, _ "This closes itself in 2 seconds", vbOKCancel) 'MsgBox Resp If Resp = vbCancel Then MsgBox "cancel was hit" End If End Sub ***************************************** End of Dave's post I tried it both as a separate Sub that was called from the Workbook_Open() and as just the line of code in the Workbook_Open() (both times before the SetTime procedure is called), but although the Sub works on its own in an empty Workbook, it hangs until manually dismissed when used with the other code. mmmmm.... I may just have to take him by surprise <g -- Regards, Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "Paul B" wrote in message ... Mike, not sure where I got this code but this should get you started this will auto-close the workbook after 20 seconds of inactivity '*************************************** 'put this in a standard module Dim DownTime As Date Sub SetTime() DownTime = Now + TimeValue("00:00:20") Application.OnTime DownTime, "ShutDown" End Sub Sub ShutDown() ThisWorkbook.Save ThisWorkbook.Close End Sub Sub Disable() On Error Resume Next Application.OnTime EarliestTime:=DownTime, Procedu="ShutDown", Schedule:=False End Sub '******************************************* '************************************ 'put this in thisworkbook Private Sub Workbook_Open() MsgBox "This workbook will auto-close after 20 seconds of inactivity" Call SetTime End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Call Disable End Sub Private Sub Workbook_SheetCalculate(ByVal Sh As Object) Call Disable Call SetTime End Sub Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range) Call Disable Call SetTime End Sub '***************************** -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Mike The Newb" wrote in message ... I have a file that only allows one user at a time to have write/update authority (first one in locks it up); there are five people with this modification authority. The problem is one of them opening it and then walking away for an extended period of time and thus the others cannot input their updates. I do not want to make it into a shared file. Similar to my company's auto password protected screen saver (kicks on after "x" number of inactive minutes), I would to have the file save itself and close if a write/update user hasn't navigated in the file via keyboard or mouse in say the last three minutes. Ideally, anyone who opened the file with "read only" clearance (approximately a dozen users preset as Read Only) would not get "kicked out" as they are not disrupting anyone. Any clues / insight? Regards, Mike |
"Kicking Out" Inactive Users
"Paul B" wrote in message
... But there is another drawback, if the "bloody minded individual" walks away from the workbook, or wants to keep it open just for them, they could leave the workbook in edit, double click or F2 in a cell and the code will not work. :( Never thought of that - I just hope that he doesn't read these NG's <g Thanks for the reply Paul -- Regards, Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk |
"Kicking Out" Inactive Users / Help !!!
Hello Paul, The code works but with some problems.
1. The workbook closes even when I'm togling between tabs on the workbook. 2. The workbook closes when I'm updating my user form, just after filing up 6 of my 15 boxes. This is my main source of data-feed onto the workbook. Regardless of 'activity' it just kicks-out the user. How do I get around this ? ----------------------- "Paul B" wrote: Mike, just tested and it works for me, try taking out the lines ThisWorkbook.Save ThisWorkbook.Close and replacing with this ThisWorkbook.Close True -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Mike The Newb" wrote in message ... Paul - almost there! Paul - almost there. The code works great up until ThisWorkbook.Close and I get a "code interrupted" pop up; when I choose Debug it highlights the ThisWorkbook.Close and if I hit continue it runs its course and closes the file. Oddly, it does close itself, without any issues, if I am not in an Excel window watching it do its thing. Dim DownTime As Date Sub SetTime() DownTime = Now + TimeValue("00:00:20") Application.OnTime DownTime, "ShutDown" End Sub Sub ShutDown() ThisWorkbook.Save ThisWorkbook.Close End Sub Sub Disable() On Error Resume Next Application.OnTime EarliestTime:=DownTime, Procedu="ShutDown", Schedule:=False End Sub Regards, Mike "Paul B" wrote: Mike, click on your workbook on the left side, go up to insert ,module put that code in there, you should see the thisworkbook module, you may also what to have a look here on getting started with macros http://www.mvps.org/dmcritchie/excel/getstarted.htm -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Mike The Newb" wrote in message ... Paul - the code looks usable enough but I don't see a Standard Module. I can find the Workbook but I only see Modules 1-10 in the VBE windows on the left side of the screen in one existing file and a newly created file shows no Modules listed at all. What is the Standard Module and where can I find it? Just a Newb like the nickname implies. Thank you for your insight and patience. Regards, Mike "Paul B" wrote: Mike, not sure where I got this code but this should get you started this will auto-close the workbook after 20 seconds of inactivity '*************************************** 'put this in a standard module Dim DownTime As Date Sub SetTime() DownTime = Now + TimeValue("00:00:20") Application.OnTime DownTime, "ShutDown" End Sub Sub ShutDown() ThisWorkbook.Save ThisWorkbook.Close End Sub Sub Disable() On Error Resume Next Application.OnTime EarliestTime:=DownTime, Procedu="ShutDown", Schedule:=False End Sub '******************************************* '************************************ 'put this in thisworkbook Private Sub Workbook_Open() MsgBox "This workbook will auto-close after 20 seconds of inactivity" Call SetTime End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Call Disable End Sub Private Sub Workbook_SheetCalculate(ByVal Sh As Object) Call Disable Call SetTime End Sub Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range) Call Disable Call SetTime End Sub '***************************** -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Mike The Newb" wrote in message ... I have a file that only allows one user at a time to have write/update authority (first one in locks it up); there are five people with this modification authority. The problem is one of them opening it and then walking away for an extended period of time and thus the others cannot input their updates. I do not want to make it into a shared file. Similar to my company's auto password protected screen saver (kicks on after "x" number of inactive minutes), I would to have the file save itself and close if a write/update user hasn't navigated in the file via keyboard or mouse in say the last three minutes. Ideally, anyone who opened the file with "read only" clearance (approximately a dozen users preset as Read Only) would not get "kicked out" as they are not disrupting anyone. Any clues / insight? Regards, Mike |
"Kicking Out" Inactive Users / Help !!!
Awaiting Answer please ? Looks like I wil not be able to implement this
otherwise. Apreciate all help. Many Thanks Matts "Matts" wrote: Hello Paul, The code works but with some problems. 1. The workbook closes even when I'm togling between tabs on the workbook. 2. The workbook closes when I'm updating my user form, just after filing up 6 of my 15 boxes. This is my main source of data-feed onto the workbook. Regardless of 'activity' it just kicks-out the user. How do I get around this ? ----------------------- "Paul B" wrote: Mike, just tested and it works for me, try taking out the lines ThisWorkbook.Save ThisWorkbook.Close and replacing with this ThisWorkbook.Close True -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Mike The Newb" wrote in message ... Paul - almost there! Paul - almost there. The code works great up until ThisWorkbook.Close and I get a "code interrupted" pop up; when I choose Debug it highlights the ThisWorkbook.Close and if I hit continue it runs its course and closes the file. Oddly, it does close itself, without any issues, if I am not in an Excel window watching it do its thing. Dim DownTime As Date Sub SetTime() DownTime = Now + TimeValue("00:00:20") Application.OnTime DownTime, "ShutDown" End Sub Sub ShutDown() ThisWorkbook.Save ThisWorkbook.Close End Sub Sub Disable() On Error Resume Next Application.OnTime EarliestTime:=DownTime, Procedu="ShutDown", Schedule:=False End Sub Regards, Mike "Paul B" wrote: Mike, click on your workbook on the left side, go up to insert ,module put that code in there, you should see the thisworkbook module, you may also what to have a look here on getting started with macros http://www.mvps.org/dmcritchie/excel/getstarted.htm -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Mike The Newb" wrote in message ... Paul - the code looks usable enough but I don't see a Standard Module. I can find the Workbook but I only see Modules 1-10 in the VBE windows on the left side of the screen in one existing file and a newly created file shows no Modules listed at all. What is the Standard Module and where can I find it? Just a Newb like the nickname implies. Thank you for your insight and patience. Regards, Mike "Paul B" wrote: Mike, not sure where I got this code but this should get you started this will auto-close the workbook after 20 seconds of inactivity '*************************************** 'put this in a standard module Dim DownTime As Date Sub SetTime() DownTime = Now + TimeValue("00:00:20") Application.OnTime DownTime, "ShutDown" End Sub Sub ShutDown() ThisWorkbook.Save ThisWorkbook.Close End Sub Sub Disable() On Error Resume Next Application.OnTime EarliestTime:=DownTime, Procedu="ShutDown", Schedule:=False End Sub '******************************************* '************************************ 'put this in thisworkbook Private Sub Workbook_Open() MsgBox "This workbook will auto-close after 20 seconds of inactivity" Call SetTime End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Call Disable End Sub Private Sub Workbook_SheetCalculate(ByVal Sh As Object) Call Disable Call SetTime End Sub Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range) Call Disable Call SetTime End Sub '***************************** -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Mike The Newb" wrote in message ... I have a file that only allows one user at a time to have write/update authority (first one in locks it up); there are five people with this modification authority. The problem is one of them opening it and then walking away for an extended period of time and thus the others cannot input their updates. I do not want to make it into a shared file. Similar to my company's auto password protected screen saver (kicks on after "x" number of inactive minutes), I would to have the file save itself and close if a write/update user hasn't navigated in the file via keyboard or mouse in say the last three minutes. Ideally, anyone who opened the file with "read only" clearance (approximately a dozen users preset as Read Only) would not get "kicked out" as they are not disrupting anyone. Any clues / insight? Regards, Mike |
| All times are GMT +1. The time now is 04:28 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com