Save Problem

Hi All,

The macros below are to save my workbook under conditions but can not succeed to execute as I wish. 1st code is to disable normal save of excel, 2nd code is to be used from CommandButton. What I want to do it:

1) Save and Close workbook if workbook name is different than "PACKAGE CALCULATOR" without any prompt screen
2) If workbook name is "PACKAGE CALCULATOR", open Save As pop-up but when Cancel Button pressed do not close workbook
3) If workbook name is "PACKAGE CALCULATOR", open Save As pop-up if new file name entered and pressed OK than save and close workbook

Thanks for all kindest helps.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

If myFlg = True Then Exit Sub

Application.Run "StopAllEvents"

Cancel = True

MsgBox "Please use EXIT Button to Close", 48, "WARNING"

ActiveWorkbook.Unprotect

Application.MoveAfterReturnDirection = MARD

ActiveWorkbook.Protect, Structure:=True, Windows:=False

Application.Run "ResetAllEvents"

End Sub

Private Sub CloseAll()

Application.EnableEvents = False

Application.DisplayAlerts = False

Dim fileSavename As String

If ActiveWorkbook.Name <> "PACKAGE CALCULATOR" Then

Application.Run "ProtectAll"
Application.Run "ProtectWB"

Application.Goto Reference:="R1C2"

ActiveWorkbook.Close savechanges:=False

End If

If ActiveWorkbook.Name = "PACKAGE CALCULATOR" Then

fileSavename = Application.GetSaveAsFilename(fileFilter:="xlsm Files (*.xlsm), *.xlsm")
If fileSavename = "False" Then

Exit Sub

ElseIf ActiveWorkbook.Name = "PACKAGE CALCULATOR" Then
fileSavename = Application.GetSaveAsFilename(fileFilter:="xlsm Files (*.xlsm), *.xlsm")
If fileSavename <> "False" Then
Application.Run "ProtectAll"
Application.Run "ProtectWB"
Application.Goto Reference:="R1C2"
ActiveWorkbook.SaveAs fileSavename, FileFormat:=52
ActiveWorkbook.Close

End If
End If
End If

Application.DisplayAlerts = True

Application.EnableEvents = True

End Sub

Hi Jazzer,

• Yes Macro works

• If workbook name is different from "PACKAGE CALCULATOR" it saves & close workbook / It' s OK

• If workbook name is "PACKAGE CALCULATOR" it opens saveas dialog and save & close after enter a file name then hit OK Button as well / It' s OK

• If workbook name is "PACKAGE CALCULATOR" and if I hit cancel button it also close the workbook but I need not to close be back to ActiveSheet / It' s NOT OK

• No hang no error message at all

• The wrong result occurs in 3rd condition only, normally it should be back to Active Sheet by closing the saveas dialog

Hope information is good enough to understand my problem
Regards