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

roelandwatteeuw

Board Regular
Joined
Feb 20, 2015
Messages
55
Office Version
  1. 365
Platform
  1. Windows
Hi all

You probably all know the AutoSave in Excel.

1602495885777.png


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

VBA Code:
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.

1602496085954.png


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:
VBA 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


VBA Code:
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

Well-known Member
Joined
Sep 10, 2010
Messages
1,105
Office Version
  1. 2013
Platform
  1. Windows
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:

VBA Code:
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.

VBA Code:
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
 
Solution

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

roelandwatteeuw

Board Regular
Joined
Feb 20, 2015
Messages
55
Office Version
  1. 365
Platform
  1. Windows
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. 😅

VBA Code:
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

Well-known Member
Joined
Sep 10, 2010
Messages
1,105
Office Version
  1. 2013
Platform
  1. Windows
Glad all is sorted now (y)
"The bird is known by his note, a man by his word", as long as it works for you it's good.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,492
Messages
5,548,362
Members
410,828
Latest member
A9Bosv3
Top