In my previous post I created a macro from scratch that saved and closed the current file. The macro required that the file had been saved before and wasn’t read only. This post handles those two situations so you can run the macro on any file and it will only work when required.

The macro we created/recorded previously is shown below.

Sub SaveAndClose()
'
' SaveAndClose Macro
' Saves and closes the current file
'
'
ActiveWorkbook.Save
ActiveWindow.Close
End Sub

If you created the macro from the previous post, you can see the code by

• pressing Alt + F11
• in the new window that opens click the Tools menu
• choose Macros
• in the Macros in drop down select VBAProject(PERSONAL.XLSB)
• then scroll to find the SaveAndClose macro
• select it and click the Edit button

We can add some code to confirm the active file is not read-only and has been saved before.

Sub SaveAndClose2()
'
' SaveAndClose Macro
' Saves and closes the current file
' and it confirms the file in not read-only and it has been saved before
'
If Not ActiveWorkbook.ReadOnly And Len(ActiveWorkbook.Path) > 0 Then
ActiveWorkbook.Save
ActiveWindow.Close
End If
 
End Sub

The If statement has two conditions.

The code ActiveWorkbook.ReadOnly is a property of the file that returns True if the file is read-only. Because we want only to save files with False as the ReadOnly property we precede the property with Not.

Not switches True to False and False to True. The If statement requires a True to accept the file, hence we have to switched the ReadOnly property.

The Len function finds the length of the file’s Path. If the file has a Path it will have a length, which also means it has been saved, so if the length of the Path is greater than zero then the file has been saved before.

Because we used And between the two conditions, both conditions have to be met (True) for the Save and Close to occur.

The above macro will not try to save a read-only file or a file that has yet to be saved.