Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have Excel 2010. I am experiencing situations where people leave work for the day and leave an Excel workbook on a shared file server open exclusively; as a result, other users are unable to edit the workbook. How can I mitigate this problem?

share|improve this question

1 Answer 1

The recommended solution to your problem is to allow multiple users to edit the Excel spreadsheet at the same time. See About shared workbooks in the Excel help for details. The crux of this approach is to go to the Review tab in Excel's Ribbon, then click the "Share Workbook" button under the "Changes" section.

By all means, attempt to use the "Shared Workbooks" feature as a primary solution. Only read further if you have already tried Shared Workbooks and you are unable to get it to satisfy your needs.

If that won't work in your situation for some reason (certain workbook features aren't compatible with shared workbooks), try this VBA solution:


IMHO that for reasons of safeguarding your data this is a most unwise thing to do.

Imagine the scenario where a user has accidentally deleted lots of data and is sitting there head in hands wondering what to do and when to confess. Now we all know you can simply close the workbook without saving changes and we're out of trouble but while considering the next step this code executes and your now going to have a very bad day. That said try this:-

ALT+F11 to open VB editor. Double click 'ThisWorkbook' and paste the first 2 modules in on the right.

Private Sub Workbook_Open()
    StartTimer
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    StartTimer
End Sub

Now right click 'ThisWorkbook' insert module and paste this code in. Save close and re-open the workbook and after 15 minutes of idle time it will save and close automatically.

Const idleTime = 900 'seconds
Dim Start
Sub StartTimer()
    Start = Timer
    Do While Timer < Start + idleTime
        DoEvents
    Loop
    Application.DisplayAlerts = False
    ActiveWorkbook.Close SaveChanges:=True
    Application.DisplayAlerts = True
End Sub
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.