当光标在单元格中处于活动状态时，Excel自动保存宏不执行 [英] Excel auto save macro doesn't execute when cursor is active in a cell

问题描述

我有一个宏，该宏每5分钟自动保存一次工作簿，以避免丢失数据.该工作簿带有数据连接，可以从PLC控制器收集生产数据，生产操作员也可以在工作簿中输入注释.

I have a macro which auto saves the workbook every 5 minutes to avoid losing data. The workbook is set up with a data connection to collect production data from a PLC controller and production operators can also enter notes into the workbook.

问题在于，如果有人开始输入便条，但没有通过按Enter或Tab或单击其他单元格来确认输入，则自动保存宏将不会执行，并且工作簿将不会被保存，直到焦点出现为止更改为另一个单元格.

The problem is that if someone started entering a note but didn't confirm the entry by pressing enter or tab or by clicking on a different cell then the auto save macro will not execute and the workbook will not be saved until the focus changes to another cell.

我试图在执行save语句之前立即更改宏中的活动单元格，但这没有用.在尝试保存文件之前，是否可以接受输入的(但未确认的)单元格内容?还有我没想到的解决方案吗?

I tried to change the active cell in the macro right before executing the save statement, but that didn't work. Is there a way to accept the entered (but not confirmed) cell contents before attempting to save the file? Is there another solution that I haven't thought about?

这是宏代码:

Sub AutoSaveAs()
    dTime = Time + TimeValue("00:05:00")
    With Application
        .OnTime dTime, "AutoSaveAs"
        .EnableEvents = False
        .DisplayAlerts = False
        ActiveCell.Offset(1, 0).Select 'This is the code where I tried to change the active cell
        ThisWorkbook.SaveAs "//ThisFilePath/ThisWorkbookName"
        .EnableEvents = True
    End With
End Sub

推荐答案

在编辑单元格时无法运行宏.您需要用户单击另一个单元格或按Enter键.

You cannot run a macro while editing a cell. You need user to click another cell or press enter.

查看众多搜索结果之一: https://stackoverflow.com/questions/26848266/start-vba-macro-何时编辑单元格

See this one of many search result: https://stackoverflow.com/questions/26848266/start-vba-macro-when-editing-a-cellunles

这篇关于当光标在单元格中处于活动状态时，Excel自动保存宏不执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！