Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Call CustomSave(SaveAsUI)
Cancel = True
End Sub
Private Sub Workbook_Open()
'Unhide all worksheets
Application.ScreenUpdating = False
Call ShowSheets
Application.ScreenUpdating = True
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Turn off events to prevent unwanted loops
Application.EnableEvents = False
'Evaluate if workbook is saved and emulate default propmts
With ThisWorkbook
If Not .Saved Then
Select Case MsgBox("Do you want to save the changes you made to '" & .Name & "'?", _
vbYesNoCancel + vbExclamation)
Case Is = vbYes
'Call customized save routine
Call CustomSave
Case Is = vbNo
'Do not save
Case Is = vbCancel
'Set up procedure to cancel close
Cancel = True
End Select
End If
'If Cancel was clicked, turn events back on and cancel close,
'otherwise close the workbook without saving further changes
If Not Cancel = True Then
.Saved = True
Application.EnableEvents = True
.Close savechanges:=False
Else
Application.EnableEvents = True
End If
End With
End Sub
Sub CustomSave(Optional SaveAs As Boolean)
Dim ws As Worksheet, aWs As Worksheet, newFname As String
Dim ans As String
'Turn off screen flashing
Application.ScreenUpdating = False
Set aWs = ActiveSheet
'Hide all sheets
Call HideSheets
If Worksheets("BAU_Form").Range("D9").Value = "" Then
SaveAs = False
MsgBox ("Save as has been disabled")
Else
'Save workbook directly or prompt for saveas filename
If SaveAs = True Then
newFname = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")
If Not newFname = "False" Then ThisWorkbook.SaveAs newFname
Else
ThisWorkbook.Save
End If
End If
'Restore file to where user was
Call ShowSheets
aWs.Activate
'Restore screen updates
Application.ScreenUpdating = True
End Sub
Private Sub ShowSheets()
'Show all worksheets except the macro welcome screen
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = "Macros" Then ws.Visible = xlSheetVisible
Next ws
Worksheets("Macros").Visible = xlSheetHidden
End Sub
Private Sub HideSheets()
'Side all workshhets except the macros welcome screen
Dim ws As Worksheet
Worksheets("Macros").Visible = xlSheetVisible
For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = "Macros" Then ws.Visible = xlSheetHidden
Next ws
Worksheets("Macros").Activate
End Sub