none
Code that would prompt a user to get out of an Excel spreadsheet RRS feed

  • Question

  • 

    Hello.

     I am looking for VBA code that would prompt a user to get out of an Excel spreadsheet (when they have forgotten and left it open on their computer) when another user is trying to access that same spreadsheet. If this is not possible, maybe there is an alternative? Thank you, James

    Tuesday, July 28, 2015 2:53 PM

Answers

  • You can close a workbook after some set amount of time of no activity.  

    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 - change the 00:20:00 to how long you want to allow no activity prior to closing the workbook:

    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.

    Tuesday, July 28, 2015 6:22 PM
  • Imagine if your co-worker opened the file and left for the day, and you needed to do your work, but could not access the file.  As long as everybody understands the workbook is auto-closing and auto-saving, then they should be fine with the result.

    Thursday, July 30, 2015 2:31 PM

All replies

  • You can close a workbook after some set amount of time of no activity.  

    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 - change the 00:20:00 to how long you want to allow no activity prior to closing the workbook:

    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.

    Tuesday, July 28, 2015 6:22 PM
  • Hello,

    Bernie this might work, but imagen you did some work in it, had a meeting or something and when you come back your excel is gone. This is not user-friendly (if it's a word).

    I think you should not allow users to open this in write modus in the first place.

    Best regards,

    Wouter

    Thursday, July 30, 2015 1:17 PM
  • Imagine if your co-worker opened the file and left for the day, and you needed to do your work, but could not access the file.  As long as everybody understands the workbook is auto-closing and auto-saving, then they should be fine with the result.

    Thursday, July 30, 2015 2:31 PM
  • You can close a workbook after some set amount of time of no activity.  

    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 - change the 00:20:00 to how long you want to allow no activity prior to closing the workbook:

    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.


    Thanks Bernie!
    Thursday, July 30, 2015 3:34 PM