Auto Log out of shared Spreadsheets

DPChristman

Board Regular
Joined
Sep 4, 2012
Messages
171
Office Version
  1. 365
Platform
  1. Windows
I use a lot of shared spreadsheets for tracking activity of my field colleagues.

The user will open the spreadsheet, assuming no one else is not already in the spreadsheet, and enter their information.

Then when they are done, they (are supposed to) save and get out of the spreadsheet.

On a regular basis, the user will forget the get out of the spreadsheet, and no one can get in until they do.

Is there a way to have a forced close of the document after a certain period of time with no activity(ex: 15-20 minutes)?

I am thinking something like a macro, but it would be above my skill set.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Hi,

Yours is a common problem with users leaving a shared workbook open preventing others having read/write access. There are plenty of suggested codes you can search for to try an overcome this like this one for example: How to save and close workbook after inactivity for a certain amount of time?.

Which, after a period of inactivity, will automatically close the workbook – but this does of course, rely on your users’ enabling macros when opening your workbook.

Other possible solution

- create a master workbook (database) on your network - your user’s workbooks then become template copy that incorporates code to read /write their data to it. This way, they can leave their copy open all day without affecting other’s ability to access / write data to the master.

- your profile does not contain operating system / office version but for work environment, you could investigate co-authoring available with office 365



Others here may have further suggestions



Hope Helpful



Dave
 
Upvote 0
Solution
Hi,

Yours is a common problem with users leaving a shared workbook open preventing others having read/write access. There are plenty of suggested codes you can search for to try an overcome this like this one for example: How to save and close workbook after inactivity for a certain amount of time?.

Which, after a period of inactivity, will automatically close the workbook – but this does of course, rely on your users’ enabling macros when opening your workbook.

Other possible solution

- create a master workbook (database) on your network - your user’s workbooks then become template copy that incorporates code to read /write their data to it. This way, they can leave their copy open all day without affecting other’s ability to access / write data to the master.

- your profile does not contain operating system / office version but for work environment, you could investigate co-authoring available with office 365



Others here may have further suggestions



Hope Helpful



Dave
Awesome, I think the script below that you referenced is what I will use, unless you see a problem with it. It seems easy enough to use and edit. I prefer not to get multiple copies of a template.

Dim CloseTime As Date
Sub TimeSetting()
CloseTime = Now + TimeValue("00:00:15")
On Error Resume Next
Application.OnTime EarliestTime:=CloseTime, _
Procedure:="SavedAndClose", Schedule:=True
End Sub
Sub TimeStop()
On Error Resume Next
Application.OnTime EarliestTime:=CloseTime, _
Procedure:="SavedAndClose", Schedule:=False
End Sub
Sub SavedAndClose()
ActiveWorkbook.Close Savechanges:=True
End Sub

Fortunately the majority of the users have little to no excel chops, so they will basically do as instructed, hopefully.

Yeah I always forget the Operating System, but it is windows 10, and office 365
 
Upvote 0
hi,
glad suggestions offer some help - just need to find something that works for your particular need.

You should update your profile with the operating system & office version in use as this helps those offering solutions to taylor their responses.

Appreciate your feedback

Dave
 
Upvote 0
hi,
glad suggestions offer some help - just need to find something that works for your particular need.

You should update your profile with the operating system & office version in use as this helps those offering solutions to taylor their responses.

Appreciate your feedback

Dave
I created a test spreadsheet using the macro above. The process does not seem to work.

The instructions are:
1. Enable the workbook you want to automatically save and close after inactivity for a certain seconds, and press Alt + F11 keys to open Microsoft Visual Basic for Applications window.
2. Click Insert > Module to create a Module script, and paste below code to it. See screenshot:

Dim CloseTime As Date
Sub TimeSetting()
CloseTime = Now + TimeValue("00:00:15")
On Error Resume Next
Application.OnTime EarliestTime:=CloseTime, _
Procedure:="SavedAndClose", Schedule:=True
End Sub
Sub TimeStop()
On Error Resume Next
Application.OnTime EarliestTime:=CloseTime, _
Procedure:="SavedAndClose", Schedule:=False
End Sub
Sub SavedAndClose()
ActiveWorkbook.Close Savechanges:=True
End Sub

3. Then in the Project Explorer pane, double click This Workbook, and paste below code to the beside script. See screenshot:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call TimeStop
End Sub

Private Sub Workbook_Open()
Call TimeSetting
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Call TimeStop
Call TimeSetting
End Sub

4. Go to double click at the module you inserted in step 2, and press F5 key to run the code. See screenshot:
5. Then after 15 seconds, there is a dialog popping out for remind you saving the workbook, and click Yes to save and close the workbook.

I followed these steps to the letter. However, when I click press F5 from step 4, the whole script goes away.

I saved as a macro-enabled spreadsheet, and it opens fine, but it does not close after the time period.

Any ideas?
 
Upvote 0
Hi,
Personally, I have never used such codes for shared workbooks - In my working days we connected users workbook to our corporate database (mainly Oracle but sometimes Access for smaller requirements) which overcome the issues of users sharing data.

The link was just a suggestion for one of many that can be found on web - I have tried the code & seemed to work OK

These codes MUST be in a STANDARD module

VBA Code:
Dim CloseTime       As Date
Sub TimeSetting()
    CloseTime = Now + TimeValue("00:00:15")
    On Error Resume Next
    Application.OnTime EarliestTime:=CloseTime, _
                       Procedure:="SavedAndClose", Schedule:=True
End Sub
Sub TimeStop()
    On Error Resume Next
    Application.OnTime EarliestTime:=CloseTime, _
                       Procedure:="SavedAndClose", Schedule:=False
End Sub
Sub SavedAndClose()
    ThisWorkbook.Close Savechanges:=True
End Sub

These Codes MUST be in the ThisWorkbook code page

VBA Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call TimeStop
End Sub

Private Sub Workbook_Open()
    Call TimeSetting
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Call TimeStop
    Call TimeSetting
End Sub

Can only suggest you trying saving & closing your test workbook & then open it again & see if code performs in manner intended.

Dave
 
Upvote 0
I created a test spreadsheet using the macro above. The process does not seem to work.

The instructions are:
1. Enable the workbook you want to automatically save and close after inactivity for a certain seconds, and press Alt + F11 keys to open Microsoft Visual Basic for Applications window.
2. Click Insert > Module to create a Module script, and paste below code to it. See screenshot:

Dim CloseTime As Date
Sub TimeSetting()
CloseTime = Now + TimeValue("00:00:15")
On Error Resume Next
Application.OnTime EarliestTime:=CloseTime, _
Procedure:="SavedAndClose", Schedule:=True
End Sub
Sub TimeStop()
On Error Resume Next
Application.OnTime EarliestTime:=CloseTime, _
Procedure:="SavedAndClose", Schedule:=False
End Sub
Sub SavedAndClose()
ActiveWorkbook.Close Savechanges:=True
End Sub

3. Then in the Project Explorer pane, double click This Workbook, and paste below code to the beside script. See screenshot:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call TimeStop
End Sub

Private Sub Workbook_Open()
Call TimeSetting
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Call TimeStop
Call TimeSetting
End Sub

4. Go to double click at the module you inserted in step 2, and press F5 key to run the code. See screenshot:
5. Then after 15 seconds, there is a dialog popping out for remind you saving the workbook, and click Yes to save and close the workbook.

I followed these steps to the letter. However, when I click press F5 from step 4, the whole script goes away.

I saved as a macro-enabled spreadsheet, and it opens fine, but it does not close after the time period.

Any ideas?
Update: I got it to work, and found the timeout was actually set to 15 seconds, not 15 minutes.
Hi,
Personally, I have never used such codes for shared workbooks - In my working days we connected users workbook to our corporate database (mainly Oracle but sometimes Access for smaller requirements) which overcome the issues of users sharing data.

The link was just a suggestion for one of many that can be found on web - I have tried the code & seemed to work OK

These codes MUST be in a STANDARD module

VBA Code:
Dim CloseTime       As Date
Sub TimeSetting()
    CloseTime = Now + TimeValue("00:00:15")
    On Error Resume Next
    Application.OnTime EarliestTime:=CloseTime, _
                       Procedure:="SavedAndClose", Schedule:=True
End Sub
Sub TimeStop()
    On Error Resume Next
    Application.OnTime EarliestTime:=CloseTime, _
                       Procedure:="SavedAndClose", Schedule:=False
End Sub
Sub SavedAndClose()
    ThisWorkbook.Close Savechanges:=True
End Sub

These Codes MUST be in the ThisWorkbook code page

VBA Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call TimeStop
End Sub

Private Sub Workbook_Open()
    Call TimeSetting
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Call TimeStop
    Call TimeSetting
End Sub

Can only suggest you trying saving & closing your test workbook & then open it again & see if code performs in manner intended.

Dave
Yes, thanks. I actually got it to work (don't ask me what I did). Then it was just a matter of setting the timeout right.

Thank again
 
Upvote 0
hi Dave i am from japan @dmt32
i also useing this code and one more thing ineed how to luck it? can you help me?

my condition is
i want to set the time every first thursDay and third thursDay 0:00 o'clock every month And luck it with PassWord
when another person want to open it then they need to unlock it with PW.
if you cant understand
"sorry for my english"
 
Upvote 0
Hi,
you should start your own thread on forum (with link to this one if needed) to allow others here to offer assistance.

Dave
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top