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]

I was not very wrong, strange that the code of post # 6 did not reveal this.
Perhaps the difference between using ActiveWorkbook (your code) and ThisWorkbook (my code).
Whatever the case, great news!

The temporary overruling of the Workbook_BeforeSave event (preventing the custom alert) can be done in the same way (instead of reading a cell value).
For example:

Private Sub SaveThisProjectAndClose()

    Dim vFileName   As Variant
    
    With ThisWorkbook
        Application.EnableEvents = False
        Application.DisplayAlerts = False
        If Len(.Path) = 0 Then
SUB_GetFileName:
            vFileName = Application.GetSaveAsFilename(FileFilter:="Macro Enabled Workbook (*.xlsm), *.xlsm", Title:="Save as")
            If vFileName <> False Then
                .SaveAs Filename:=vFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
            Else
                GoTo SUB_GetFileName
            End If
        Else
            .Save
        End If
        .SaveAs Filename:=Left(.FullName, (InStrRev(.FullName, "."))) & "xltm", FileFormat:=xlOpenXMLTemplateMacroEnabled
         Application.DisplayAlerts = True
         Application.EnableEvents = True
        ThisWorkbook.Close
    End With
End Sub


Private Sub CloseProjectWithoutSaving()

    ThisWorkbook.Saved = True
    ThisWorkbook.Close
End Sub

Finally, consider the following. A workbook can be saved in several ways: Ctrl+S, disk button on QAT, Ribbon > File > Save (Save As) and finally, when the file has been changed, through the window close button [X]. After all, some users have made a habit of saving while closing, so this should be taken into account to avoid users are accidentally closing a file while it has not yet been saved.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
        
        Cancel = True
        MsgBox "Saving this file this way is not possible, so it's NOT saved yet!" & vbNewLine & _
               "You are supposed to use the custom Save button on the worksheet."
End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)

    If ThisWorkbook.Saved Then
        Cancel = False
    Else
        MsgBox "You are trying to close this file but it has not been saved yet!" & vbCrLf & _
               "Use the custom Save button on the worksheet."
        Cancel = True
    End If
End Sub

 

[roelandwatteeuw]

I have some code in it to avoid closing without saving.

Probably not as good as your code, but it works
And yes, again I'm using a cell value. ?

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'Check if changes have been made. If so and file isn't saved, ask id the user really wants to close the file
If ThisWorkbook.Saved = False And Range("N1").Value = 1 Then        'If changes are made, the value in N1 will be 1 (N1 is hidden in the Workbook)
   Useranswer = MsgBox("The document has been changed since your last save." & vbCrLf _
        & "Do you really want to quit this document?", vbYesNo, "Afsluiten?")
            
        If Useranswer = vbNo Then
            Cancel = True
            Exit Sub
        End If
End If

ThisWorkbook.Saved = True
End Sub

I can see you are much more experienced in writing code.
I write code like a 4-year-old-kid speaks.
A lot of messy words, but just enough to understand the message and get what you want. And sometimes you need a little help to achieve what you want. ?

 

[GWteB]

Glad all is sorted now
"The bird is known by his note, a man by his word", as long as it works for you it's good.