Automatically save on insert/modify cell data.

[ossosso]

Hi,
let me say that I red a lot of topics on how to figure out this problem. I've collected some VBA code to try, but with no success.

I would like that an .xlsm file is automatically saved each time that I add or update content into a cell.

1. I've created a new .xlsm file.
2. Right click on the sheet name on the bottom and click on "view code"
3. Paste the code, pressed ctrl s to save VBA code and close
4. Once in excel I type something in one cell.
5. I press ALT+F4 to close and the application asks me if I want to save the document. But it shouldn't. Why it doesn't save automatically ?

Here the codes I've tried, one by one.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("A1:AA1000")) Is Nothing Then
        ThisWorkbook.Save
    End If
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
ActiveWorkbook.Save
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("A1:D20")) Is Nothing Then
        ThisWorkbook.Save
    End If
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range
    ActiveWorkbook.Save
End Sub

Option Explicit
Private Sub Workbook_Open()
ONTIMER_S = Now() + TimeValue("00:00:01)
  Application.OnTime ONTIMER_S, "SaveBook"
End Sub
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime ONTIMER_S, "SaveBook", , False
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("A1:F20"), Target) Is Nothing Then ActiveWorkbook.Save
End Sub

I'm missing something?

Thanks a lot.

[p45cal]

I tried this with your first code and it doesn't ask to save the document.
When you say 4. Once in excel I type something in one cell. that cell MUST be one that is in the A1:AA1000 range so that the save is done in the event handler.
I would temporarily add a line to the event handler like
MsgBox "File saved":

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("A1:AA1000")) Is Nothing Then
        ThisWorkbook.Save
        MsgBox "File saved"
    End If
End Sub

just so that you're sure the event has triggered and the file's been saved.

If the message box doesn't appear when you expect it to, then you'll be looking to check that Application.EnableEvents is set to True (and that some code elsewhere doesn't set it to False without resetting it to True later).

You're sure that no other event is being triggered after your Change event which changes anything elsewhere in the workbook before you close the workbook manually?
Also check that you have no BeforeSave event which is silently cancelling the save operation.

[ossosso]

Thank you so much for your help.
It doesn't work for me.

This is what I did:
1. Create a new xlsm file
2. Right lick on the sheet name in the bottom.
3. On th menu I click on "View Code"
4. Paste your code in the new window
5. Press ctrl s to save
6. Press ALT F11 to come back to the sheet.
7. Type something in cell A1 and press ENTER.
8. Any message box appears.

It seems something block the code.

I don't know how to check that Application.EnableEvents is set to True.
I am a not professional Excel User. I have no idea if some evets are activated or not. I case they are, I did it accidently.

Thank you in advance for any additional help.

My Excel version is 2016 Home and Students Versions 1806 (build 10228.20104)

[p45cal]

So we've established that the code isn't running for some reason, or more precisely, the code could be running but not getting to to the msgBox line, perhaps because the If isn't being satisfied.
So let's test whether the code is running AT ALL.
There are several ways, but this method will allow us to diagnose further:
In the code you've written, put the editing cursor somewhere in the first line of the sub (Private Sub Worksheet_Change~…), then on the keyboard press the F9 key. This should put a brown blob to the left of the line and highlight the line in brown. It's a toggle switch so pressing F9 again should remove the highlighting. You've just put a break-point into the code which should stop at that line when it tries to execute. The coding window should open and that same line highlighted in yellow when that happens.
The first question is, when you've changed a cell (in the right range), does this happen? If not then see later. If it does happen, you can then step through the code, line by line, by pressing the F8 key on the keyboard which will execute code lines, one at a time. Using this you can follow everywhere the code tries to be executed; other modules, macros/subs, functions.

If it doesn't happen, open up the Immediate pane in the coding window (Ctrl+g if you can't see it) and type:
?Application.eneableEvents
and press Enter. Do you see True or False? If False, you need to type:
Application.eneableEvent=True
and press Enter.
Then go back to your sheet and try again.

If you want, we could very probably solve this one very quickly with a TeamViewer session (Google it).

[ossosso]

Thanks again for your answer. I think I've solved.

Once in the Immediate Pane I've typed
?Application.eneableEvents
and pressed enter.
A message window appears: "Macro into this project are not active" (I use Office in Italian, but it English should sound like that).

So I've opened Excel Optios-->Protection Center-->Macro Settings
There's a radio button with 3 choices By default it was on the first one. I've moved on the last one "Enable Macro (it's better you don't do it)".

Now everything works.

I didn't thought to check it because I use other Excel documents with macro inside and they works correctly.
Probably this one is a different kind of macro.

Thanks a lot for your time and help.

[p45cal]

So I've opened Excel Optios-->Protection Center-->Macro Settings
There's a radio button with 3 choices By default it was on the first one. I've moved on the last one "Enable Macro (it's better you don't do it)".

That choice is a dangerous one. Choose option 2 instead Disable all macros with notification, then you will be given the choice each time. Much safer.