0

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.

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.

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?

This is the macro code:

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
YowE3K
  • 23,852
  • 7
  • 26
  • 40
Carl Fredrickson
  • 1
  • Does the AutoSaveAs sub not execute if the user is editing a cell, or does it execute and fail? It if just doesn't execute, then doing something inside it won't help. – Rich Holton Apr 20 '17 at 21:50
  • `OnTime` events only fire when Excel is idle. It isn't idle when Excel thinks the user is editing a cell. I think your only solution is to educate the users not to think about their edits for so long. – YowE3K Apr 20 '17 at 21:56
  • Actually events fire and code executes even if user is editing a cell. While a cell is being edited, `Application` object (and all of it's members) just becomes unresponsive, thus the code fired by the event can't set the next event, and any reference to `Application` or it's members leads to code termination with error -2147418111, but the error message can't be displayed and just muted. Some details and example code you can find in [this answer](http://stackoverflow.com/a/38134477/2165759) in second method. – omegastripes Apr 20 '17 at 23:03
  • Rich - I added a message box to see if the sub executes and it does not until the cell is no longer being edited. So yeah, adding something to the sub will not make a difference. – Carl Fredrickson Apr 21 '17 at 14:40
  • omegastripes - Thanks for the information about Application and the link to a possible workaround. – Carl Fredrickson Apr 21 '17 at 14:42

1 Answers1

0

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

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

Community
  • 1
  • 1
Tehscript
  • 2,556
  • 2
  • 13
  • 23
  • Thanks for the information. In our case I don't think we will go this route (using an ActiveX control instead of typing directly into the cell), but it's good to know that it's an option. I think our best bet is to educate the operators about finishing their notes and confirming the cell edit. – Carl Fredrickson Apr 21 '17 at 14:50