1

I'd like to save my spreadsheet every time I lock my windows pc. Is there a built-in trigger in excel (similar to on-close)? Or might I be able to get a macro to run every minute and check if my machine is in use or locked?

Community
  • 1
  • 1
Martin KS
  • 481
  • 7
  • 26

3 Answers3

1

If you really need this you can try the following workaround:

Enable auditing for the lock event in the event viewer, so that event 4800 for locking is logged, as described here.

Create a scheduled task triggered on the lock event. Choose Trigger on Event with Protocol: "Security", Source: "Microsoft-Windows-Security-Auditing" and Event-ID: "4800"

Run a vbscript that uses GetObject(, "Excel.Application") to retrieve the currently active excel session and run a macro that saves for you with Application.Run (like in this example)

Not very straight forward, but on the other hand, the easy way would just be to press ctrl+s everytime before you lock your computer.

Community
  • 1
  • 1
Syberdoor
  • 2,521
  • 1
  • 11
  • 14
  • Thank you for your answer, and for linking through to all of the guides, I'm glad you did, as I'm about to prove how lost I am without the links! You mention to "Create a scheduled task", but unfortunately I'm a little lost as to which task to use to trigger my event. [There's a lot to pick from, and none are particularly well labelled!](https://www.dropbox.com/s/n9sklkt8bcc2y7d/Scheduler.jpg?dl=0) – Martin KS Mar 08 '15 at 17:08
  • Protocol would be "Security" Source would be "Microsoft-Windows-Security-Auditing" (i hope I translated that one correctly), Event ID would be 4800 – Syberdoor Mar 08 '15 at 17:57
0

You could try this and see if this works instead of using a macro:

  • Click the Microsoft Office Button , and then click Excel Options.
  • Click Save.
  • Select the Save AutoRecover information every x minutes check box.
  • In the minutes list, specify how often you want the program to save your data and the program state.

But if you specifically want a macro I could try to form one for you.

Xanmashi
  • 167
  • 1
  • 2
  • 10
-1

Okay well add this into a standard module:

Option Explicit
Public RunTime

Sub StartTimer()
    RunTime = Now + #12:10:00 AM#
    Application.OnTime RunTime, "SaveBook", schedule:=True
End Sub

Sub SaveBook()
    ActiveWorkbook.Save
    StartTimer
End Sub

Sub StopTimer()
   On Error Resume Next
   Application.OnTime RunTime, "SaveBook", schedule:=False
End Sub

Place this in your workbook class module:

Private Sub Workbook_Open()
    StartTimer
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Then add this into your workbook class module:

Private Sub Workbook_Open()
    StartTimer
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    StopTimer
End Sub

Have fun with it and fiddle around with it

Terry Jan Reedy
  • 18,414
  • 3
  • 40
  • 52
Xanmashi
  • 167
  • 1
  • 2
  • 10
  • That will not autosave on a user locking the computer, it will just add another autosave-function. – eirikdaude Mar 06 '15 at 21:31
  • While that would be a good way of fixing the autorecover issue, it doesn't really take into account whether I'm using the workbook or not, and you've not made particularly clear the interval between saves? is `#12:10:00 AM#` every 10 minutes? or every 12 hours 10 minutes? Anyway, it's good as a fallback - is there a function _"is this workbook active"_ or _"is the screensaver running"_ that I could add to `sub SaveBook()`? – Martin KS Mar 08 '15 at 16:39