Need Help With a Macro

[pbrazeau1]

Hello,
I need help writing a macro for a spreadsheet. I would like the macro to time out a spreadsheet after ten minutes of activity. I would also like a pop up window to ask they user if they are still working, and if they do not respond to the pop up to automatically save and close the spreadsheet if the user doesn't respond to the pop up window within 30 seconds. Can anyone help with this?

 

[DanteAmor]

Check this:

VBA to close workbook after 10minutes of inactivity

I have a shared workbook on our server that is used by numerous people, problem is that someone opens it and leaves it open then goes to lunch, so no one else can edit it. So id like to close it after 10 minutes on inactivity. Can I get help on writing a VBA code to close a workbook after 10...

www.mrexcel.com

 

[Logit]

Here are a few examples for you to review :

Internxt Drive – Private & Secure Cloud Storage

Internxt Drive – Private & Secure Cloud Storage

Internxt Drive – Private & Secure Cloud Storage

 

[pbrazeau1]

First of all, I want to thank all of (profusely, I do know this is a hobby for people) you for your help. I ran into a few problems, such as the fact that if I choose to have a warning box display after inactivity, then the file would hang if there was yes or no option to be clicked which wasn't helpful. I also wanted to make very sure that changes were saved when the file was shut down after activity. This file is used by an inordinate amount of people and we have had many arguments over how to manage it (making it a shared workbook was a nightmare). I did my best to edit the code so that there is no dialogue box, and that changes were saved after ten minutes of inactivity and the file was closed. (I discovered I had to input this into the this workbook field rather than inserting a module?). It's not working, and I think it may be just an issue of myself copying and pasting and ruining the code. Could someone please help me come up with a simple command to save and close the workbook after 10 minutes of inactivity (no dialogue box required?) I am so desperate at this point I am willing to pay. Here is the code I used (unsuccessfully):

Dim DownTime As Date

Sub SetTimer()
    DownTime = Now + TimeValue("0:10:00")
    Application.OnTime EarliestTime:=DownTime, Procedure:="ShutDown", Schedule:=True
End Sub
Sub StopTimer()
    On Error Resume Next
    Application.OnTime EarliestTime:=DownTime, _
      Procedure:="ShutDown", Schedule:=False
 End Sub
Sub ShutDown()
    Application.DisplayAlerts = False
    'With ThisWorkbook
        '.Saved = True
        '.Close
    'End With
    ActiveWorkbook.Save
    ActiveWorkbook.Close
 
   End Sub

 

[Logit]

In the ThisWorkbook module :

Option Explicit

Private Sub Workbook_Open()
    Call SetTimer
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call StopTimer
    Application.DisplayAlerts = False
    ThisWorkbook.Saved = True
    Application.Visible = False
    Application.Quit
End Sub
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    Call StopTimer
    Call SetTimer
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
  ByVal Target As Excel.Range)
    Call StopTimer
    Call SetTimer
End Sub

In a Regular module :

Option Explicit


Dim DownTime As Date

Sub SetTimer()
    DownTime = Now + TimeValue("00:00:30") ''<--- change time to close here
    Application.OnTime EarliestTime:=DownTime, Procedure:="ShutDown", Schedule:=True
End Sub
Sub StopTimer()
    On Error Resume Next
    Application.OnTime EarliestTime:=DownTime, _
      Procedure:="ShutDown", Schedule:=False
 End Sub
Sub ShutDown()

    Application.DisplayAlerts = False
    With ThisWorkbook
        .Saved = True
        .Close
    End With
End Sub

Download sample workbook : Internxt Drive – Private & Secure Cloud Storage

 

[Peter_SSs]

Here are a few examples for you to review :

Internxt Drive – Private & Secure Cloud Storage

Internxt Drive – Private & Secure Cloud Storage

Internxt Drive – Private & Secure Cloud Storage

@Logit
Please review #4 of the Forum Rules
In particular these extracts from that rule

please do not answer questions by creating solutions elsewhere and then referencing those solutions via file links. We ask that you answer the question within the thread itself

do not simply create a file with a solution, and provide a link to that.

@pbrazeau1
When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you in post #4 this time.

 

[Logit]