一段时间后如何自动保存并退出工作表？ （Excel VBA） [英] How to automatically Save and Exit out of a worksheet after a set amount of time? (Excel VBA)

问题描述

如果没有活动超过5分钟，有没有办法使打开的工作表自行关闭？

所以例如：我在一个工作表上工作一段时间，然后走开20分钟，打开表格。网络上的人需要访问该表单，但不能因为我在上面。

我想要的是，在我离开我的办公桌超过5分钟后，工作表将自动保存并关闭所述工作表。

这可能吗？如果是这样的话我可以找到脚本来显示如何保存和关闭工作表，但是我还没有找到使用定时器的脚本。

解决方案

好的，原来的答案在下面，我想出了自己，经过一点点的研究。

打开开发人员部分后，您将找到工作表，将此代码放在ThisWorkbook中。这将允许您的代码在整个工作表中工作。我现在设置在那里有一个10:00分钟的初始定时器，而一个05:00分钟的定时器，如果有事后的活动。您可以将其更改为任何您想要的。

  Option Explicit 
 Private Sub Workbook_Open（）
 EndTime =现在+ TimeValue（00:10:00）
 RunTime 
 End Sub 
 
 Private Sub Workbook_SheetChange（ByVal Sh As Object，ByVal Target As Range）
如果EndTime然后
 Application.OnTime _ 
 EarliestTime：= EndTime，_ 
过程：=CloseWB，_ 
计划：= False 
 EndTime =空
 End If 
 EndTime = Now + TimeValue（00:05:00）
 RunTime 
 End Sub 
  

下面的部分需要进入一个新创建的模块，命名为任何你想要的，我的名为SaveWB

  Option Explicit 
 
 Public EndTime 
 Sub RunTime（）
 Application.OnTime _ 
 EarliestTime：= EndTime，_ 
程序：=CloseWB，_ 
 Schedule：= True 
 End Sub 
 
子Clos eWB（）
 Application.DisplayAlerts = False 
 With ThisWorkbook 
 ThisWorkbook.Close savechanges：= True 
 End with 
 End Sub 
  

我更改了代码：

 使用ThisWorkbook 
 .Save 
 .Saved = True 
 .Close 
结束
  

以上是什么。

  With ThisWorkbook 
 ThisWorkbook.Close savechanges：= True 
结束与
  

我创建的部分作品，最初发布的部分在关闭但不保存。做你喜欢的事情，按照你的想法改变它，但我很高兴我得到它的工作。

Is there a way to make an open worksheet close itself if there is no activity on it for more than 5 minutes?

So for example: I work on a worksheet for a while then walk away for 20 minutes with said sheet open. Someone on the network requires to access the sheet but can't because I'm on it.

I want it so that after me being away from my desk for more than 5 minutes the sheet will save itself and close out said sheet.

Is this possible? If so how? I can find scripts to show how to save and close a sheet, but I've yet to find one that uses a timer...

解决方案

Ok, with the original answer below, I came up with my own, after a little more research.

Once you open the developer's section you will find your sheets, place this code below into ThisWorkbook. That will allow your code to work through the entire sheet. I now it set up where there is a 10:00 minute initial timer, and a 05:00 minute timer if there is activity after the fact. You can change that to whatever you want.

Option Explicit
Private Sub Workbook_Open()
    EndTime = Now + TimeValue("00:10:00")
    RunTime
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If EndTime Then
        Application.OnTime _
                EarliestTime:=EndTime, _
                Procedure:="CloseWB", _
                Schedule:=False
        EndTime = Empty
    End If
    EndTime = Now + TimeValue("00:05:00")
    RunTime
End Sub

The part below this needs to go into a newly created module, name it whatever you want, mine is called SaveWB

Option Explicit

Public EndTime
Sub RunTime()
    Application.OnTime _
            EarliestTime:=EndTime, _
            Procedure:="CloseWB", _
            Schedule:=True
End Sub

Sub CloseWB()
    Application.DisplayAlerts = False
    With ThisWorkbook
        ThisWorkbook.Close savechanges:=True
    End With
End Sub

I changed the code from:

With ThisWorkbook
    .Save
    .Saved = True
    .Close
End With

To what was above it.

    With ThisWorkbook
        ThisWorkbook.Close savechanges:=True
    End With

The part I created works, the part that was originally posted works in closing but not saving. Do what you will with it, change it as you deem fit, but I am glad I got it working.

这篇关于一段时间后如何自动保存并退出工作表？ （Excel VBA）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！