can you set a time limit on an open Excel sheet?

  • Thread starter Thread starter Nelson
  • Start date Start date
N

Nelson

We have a couple of shared Excel worksheets on our server
that several people need access to. The problem is,
someone will open it and leave their desk and forget it is
open, thus preventing someone else from opening it except
as read-only. We don't want to turn on the concurrent
access by more than one user to avoid save conflicts- is
there a way to put a time limit on an open sheet, where
after a certain time the sheet will close itself, thereby
enabling access by others?

thanks for any help...

Nelson
 
We don't want to turn on the concurrent
access by more than one user to avoid save conflicts- is
there a way to put a time limit on an open sheet, where
after a certain time the sheet will close itself, thereby
enabling access by others?
Click to expand...

If this were possible, would you have the file saved before
it's closed or not?

Jordon
 
You could do something like this:


Code:
--------------------

Private Sub Workbook_Open()

Application.OnTime Now + TimeValue("00:00:10"), "closeBook"

End Sub
--------------------


Put the previous lines of code in the "ThisWorkbook" object and these
lines of code:


Code:
--------------------

Sub closeBook()

ActiveWorkbook.SaveAs ("C:\Documents and Settings\dmorri18\Desktop\New Folder\" & "myNewFile" & ".xls")
Application.DisplayAlerts = False
Application.Quit

End Sub
--------------------


in Module1...The first bit is telling it to run the code in Module1 10
seconds after you open the workbook...this line specifies the time to
run the save and close part in a new file in a folder called "New
Folder" on my desktop, the file is called "myNewFile.xls...this of
course doesn't save changes in the original, only in the new one.

Now + TimeValue("00:00:10")

the Now is the usual now function in excel...and the time value is self
explanatory. To change the code to have it run at a specific time of
day...say at 2:45 PM...you would use:

Application.OnTime TimeValue("2:45 PM"), "closeBook"

I hope this gets you going in the right direction....

Dave M.
 
You might end up with a disaster if the user that has the
file open, has made an undesirable change and then
went past the time limit. I can hear the scream from
across the office now... "OH NO! Why now?!?"

Jordon
 
You could easily change the macro so it only closed the spreadsheet at least
10 minutes after the last change to the spreadsheet.

Also, with sharing set, can't you undo a particular user's changes?
 
You could easily change the macro so it only closed the spreadsheet at least
10 minutes after the last change to the spreadsheet.
Click to expand...

Good thought, but I still think it's asking for trouble.
Also, with sharing set, can't you undo a particular user's changes?
Click to expand...

I don't know much about versions after 97 but I think the only
option is when changes conflict with each other.

Jordon
 
Hi Nelson, the "ThisWorkbook" object is found by hitting ALT+F11. It
will then be on the left hand side of the screen in the "VBA Project"
window along with a list of all open workbooks with their sheets. You
can just copy and paste the first part of the code directly into
"ThisWorkbook". To get a module for your workbook if you already don't
have one, just right-click the "ThisWorkbook" object, select "insert -
module", and paste the second part of the code into the module.

Again, I hope this helps....

good luck,

Dave M.
 
Back
Top