Welcome to Live View – Take the tour to learn more
Start Tour
There is currently 1 person viewing this thread.
Stow_judge
27 Jun 14 10:25
Joined:
Date Joined: 10 Mar 01
| Topic/replies: 10,954 | Blogger: Stow_judge's blog
We have a spreadsheet at work that a no. of people access. (Office 2010) I found some code on the web that closes the spreadsheet after 10 minutes.
A pain if the harris has started clicking in a cell and leaving it open on their pc for hours. Sad I was hoping then adding Application.SendKeys ("{escape}") and also tried Range("A1").Select after it, in the hope that this would exit from the selected cell and then close the spreadsheet. It doesn't. Cry

Can anyone help?


Public RunWhen As Double
Public Const NUM_MINUTES = 10

Module 1

Public Sub SaveAndClose()
Application.SendKeys ("{escape}")
Range("A1").Select
    ThisWorkbook.Close savechanges:=True
End Sub

This workbook

Private Sub Workbook_Open()
    On Error Resume Next
    Application.OnTime RunWhen, "SaveAndClose", , False
    On Error GoTo 0
    RunWhen = Now + TimeSerial(0, NUM_MINUTES, 0)
    Application.OnTime RunWhen, "SaveAndClose", , True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    Application.OnTime RunWhen, "SaveAndClose", , False
    On Error GoTo 0
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    On Error Resume Next
    Application.OnTime RunWhen, "SaveAndClose", , False
    On Error GoTo 0
    RunWhen = Now + TimeSerial(0, NUM_MINUTES, 0)
    Application.OnTime RunWhen, "SaveAndClose", , True
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
    ByVal Target As Range)

    On Error Resume Next
    Application.OnTime RunWhen, "SaveAndClose", , False
    On Error GoTo 0
    RunWhen = Now + TimeSerial(0, NUM_MINUTES, 0)
    Application.OnTime RunWhen, "SaveAndClose", , True

End Sub
Show More
Loading...
Report Stow_judge • June 27, 2014 10:26 AM BST
Should I try the Application.SendKeys ("{escape}") bit of code in one of the other sub routines?
Report Gin • June 27, 2014 10:34 AM BST
Compound Magic is the best Excel expert I have seen on here (and usually very helpful) but I haven't seen him post much lately.
Post Your Reply
<CTRL+Enter> to submit
Please login to post a reply.

Wonder

Instance ID: 13539