Disable AutoSave after 'Enable Content' but Before 'WorkbookOpen'

[roelandwatteeuw]

Hi all

You probably all know the AutoSave in Excel.

And probably a lot will know how to disable it with VBA.

Sub Workbook_Open()
    ActiveWorkbook.AutoSaveOn = False
End If

Situation:
When a user is opening this file for the first time, Excel will ask to 'Enable Content' for security reasons.

Before Enabling this option, the file will still be in AutoSave-mode.
After Enabling it will run my macro in 'Workbook_Open' in 'ThisWorkbook'.

This macro contains first off all the code to disable the AutoSave.
After this, in the same macro, an InputBox will pop-up where the user needs to fill in some info.

My file also contains a macro 'Workbook_BeforeSave'.
This to avoid users to save the file without using a 'Save-button' I made.
If they're trying to save it without the button, it will show an alert that they have to use the button.


Problem:
After completing the InputBox, Excel shows me the Alert that I have to use the button --> The alert made in the 'Workbook_BeforeSave'.

The InputBox seems to be handled before disabling the AutoSave.
So AutoSave is still On when the InputBox appears.

I can't find a solution to make it work and avoid the alert.


Some of my code:

Sub Workbook_Open()
Dim NeededInfo as String

On Error Resume Next

Sheets("RBD").Select

If ActiveWorkbook.AutoSaveOn = True Then
    ActiveWorkbook.AutoSaveOn = False
End If

NeededInfo = InputBox("Give me the info", "Information", NeededInfo)

Range("J1").Value = NeededInfo

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Cancel = True
    MsgBox "The file has not been saved!" & vbCrLf _
            & "Use the Save-Button."
End Sub

 

[GWteB]

The issue you're having is caused by Excel's so called "Safe Mode", which shows you a warning in a yellow bar. Although you're enabling editing, Safe Mode is causing you troubles here. This is why:
- when the Workbook_Open event procedure is running, Excel remains in Safe Mode;
- Safe Mode does not end until the Workbook_Open procedure has completely finished.

Needless to say Safe Mode has some characteristics / limitations:
- all manual changes to the Workbook are ignored / blocked;
- attempts to make changes through VBA (eg. Sheets(1).Select or Range("A1").Value = nn or ThisWorkbook.AutoSaveOn = False) always result in a Run-time Error;
- using UserForms (regardless Modal / Modeless) is useless;
- the workbook's window cannot be addressed.

The latter is used in the code below to determine if the workbook initially was opened in Safe Mode. See if it circumvents your problem.

Option Explicit

Private bSafeMode       As Boolean


Private Sub Workbook_Open()

    ' assume safe mode
    bSafeMode = True
    If Not Application.ActiveWindow Is Nothing Then
        MsgBox "In SafeMode this message will never be displayed..."
        bSafeMode = False
        Call ToStartWith
    End If
End Sub

Private Sub Workbook_Activate()

    If bSafeMode Then
        ' launch just once!!
        bSafeMode = False
        Call ToStarthWith
    End If
End Sub

Private Sub ToStartWith()

    Dim NeededInfo As String
    
    With ThisWorkbook
        .AutoSaveOn = False
        .Sheets("RBD").Select
        NeededInfo = InputBox("Give me the info", "Information", NeededInfo)
        Range("J1").Value = NeededInfo
    End With
End Sub

 

[roelandwatteeuw]

Hi GWteB

First off all, a big thank you for your help.
Some really useful information over there!

But... I can't figure out how to implement the Message Box where the user is informed he must use my self-made Safe-button (when he wants to safe the file with the standard-Excel-button or Ctrl+S...)

Easy said. The users can't save the file with the standard-Excel-button, only with my self-made Safe-button. (This because there are several other actions behind this button)
But I made a way to overrule this, by placing '1' in Cel A1. (e.g. in case I want to change something to the template)
So before saving, the macro checks if there is a '1' in A1. If so, it can be saved. If not, saving is cancelled and the message must show up… but off course, not in safe mode.

The code now in Workbook_BeforeSave

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If Sheets("RBD").Range("A1") <> 1 Then
    Cancel = True
    MsgBox "The file has not been saved. " & vbCrLf _
                & "Use the Save-button."
Else
    'Do Nothing
End If
End Sub

So what should change here? Now, I still get the Message Box in new documents (in safe mode).

 

[GWteB]

Now, I still get the Message Box in new documents (in safe mode).

Do you mean on opening the template?
Over here it gives me only a message when I try to save it (ie save as, since it's a copy of a template (*.xltm)) and that's as intended. After all, you have a special button with appropriate code behind it to save the workbook.

 

[roelandwatteeuw]

(The file is in *xltm - I know, not really a template, but it's used like one. Maybe I should consider changing it)

I made a simplified file.
Upload with WeTransfer:
Empty file.xlsm

Can you have a look?


This is what I do:
> Open the new file (Made a copy of it, so it will open as a new file)
> First time it will open in Safe Mode, so I press 'Enable Content' (here in Dutch 'Inhoud Inschakelen')

This is the result:
> I got the MessageBox from the Workbook_Open => OK (not needed, but easy to disable)

> I got the MessageBox from the Workbook_BeforeSave => NOT OK. This can only be displayed when I'm trying to save the file without using the red SAVE-button. Not when I'm opening the file for the first time (in Safe Mode)

> I got the Fill In Box 'NeededInfo' from the sub 'ToStartWith' => OK

Hope you can solve this...

Thx again!

 

[GWteB]

Not sure because my Excel version does not support the Auto Save feature, but I think that's the culprit. To be sure, please perform a small test. Create an empty workbook, paste the code below into the ThisWorkbook module, save the workbook as macro enabled (.xlsm) and close Excel completely. Then open the newly saved workbook and report the number shown by the message.

Option Explicit

Private iResult As Integer, iTest As Integer

Private Sub Workbook_Open()
    Dim bBool As Boolean
    With ThisWorkbook
        iTest = 2
        bBool = .AutoSaveOn
        iTest = 4
        .AutoSaveOn = True
        iTest = 8
        .AutoSaveOn = False
    End With
    MsgBox "Result value: " & iResult
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    iResult = iResult + iTest
End Sub

 

[GWteB]

In anticipation of your answer to my previous post #6 (be sure to reply on that one) the following for background information.
Although the code construction I suggested in my post # 2 was needed a few years ago due to the fact the ActiveWindow property did not return a valid Window object during Safe Mode, it looks like MS made some changes to this behavior in subsequent updates. At present Safe Mode seems to disable immediately after a click on the Enable Content button after which (unlike before) Excel executes the Workbook_Open event procedure without restrictions. This explains why (in both your and my Excel version) the message "In SafeMode this message will never be displayed ..." appears on the screen (unlike a few years ago). Anyway, I'm confident about solving your issue.

 

[roelandwatteeuw]

Result Value: 0

 

[GWteB]

Because of the code you provided in post # 1 I would expect the result to be greater than 0. Although unlikely, I had the idea for a moment that touching the AutoSaveOn property (reading/writing) would trigger a Save event, and with it displaying your custom alert. Getting rid of the mentioned alert on Open or New isn't that difficult but it has to be done in a proper way to avoid future errors. Incorrect use of Event procedures often leads to errors that are difficult to trace.
Reading back I see your comment "Some of my code". Does this mean the Workbook_Open procedure contains more code than you posted and/or are there more event procedures? If so, please post the code of the entire ThisWorkbook module and the procedures that may be called from those events. If not, also let me know.

 

[roelandwatteeuw]

Yes, my Workbook_Open contains much much more than this. And really… you don't want to see it. It's one big mess (the results of continue adding little thing to the file)

BUT!!!!!!

I FOUND A SOLUTION!!!

And it was really an easy one...

I figured out that Excel wants to "save" the file just before switching it from 'AutoSaveOn' to 'AutoSaveOff'

Then I remembered there is a code that lets you avoid other subs from running: Application.EnableEvents
With this on False, the Workbook_BeforeSafe won't run. And so it won't be activated when Excel changes the AutoSave from 'On' to 'Off'
In this way, the MessageBox (and other code) in Workbook_BeforeSafe, won't display when I open a new Workbook.

Sub Workbook_Open()

Application.EnableEvents = False

If ActiveWorkbook.AutoSaveOn = True Then
    ActiveWorkbook.AutoSaveOn = False
End If

Application.EnableEvents = True

But man, I'm very happy with your time and support you gave me.
Another big big thank you for the help!