Hi all,

I am trying to write a WorkbookBefore events so that a custom dialog box
will be shown for specific type of files. Here is the psuedo-code:

Sub App_WorkbookBeforeClose(Wb as Workbook, Cancel as Boolean)
Dim Ans as String

If Wb.Name Like "Book*" Then
Ans = MsgBox("Do you want to save the changes you made to " & wb.Name &
"?"
Select Case Ans
Case vbYes
Call ActualSave Wb, "ABCDE"
Case vbNo
Me.Saved = True
Case vbCancel
Cancel = True
End Select
End if
End Sub

Sub ActualSave(ByVal WhichBook as Workbook, byVal FileName as String)
'Handling saving routine using codes
Filename = Application.GetSaveAsFilename( _
InitialFileName:=DefaultName, _
FileFilter:="Excel Files (*.xls), *.xls")

'Disable all events, including the BeforeSave and BeforeClose events
'in order to prevent infinite loops

If Filename < "False" Then 'User did not cancel the save
Application.EnableEvents = False
On Error Resume Next 'In case the file to be saved already exist
'avoid the error
WhichBook.SaveAs Filename:=Filename, AddToMru:=True
Application.EnableEvents = True
End If
End Sub

Sub App_WorkbookBeforeSave(Wb As Workbook, SaveAsUI as Boolean, Cancel as
Boolean)
Call OtherTask 'Omitted here
End Sub

My problem I encountered is that, on triggering the WorkbookBeforeClose
event and user clicks OK to save a file AND the filename already exists,
then a dialog box asking for replacement will be shown.

If the user responded to this dialog box by clicking "No", then another
dialog box, "Do you want to save the changes you made to "Bookx"" appears,
which is not my intention.

If the user responded "Yes" instead, file will be saved but the file will
not close (which supposedly should have been).

Please advise how can I rectify the situation. Thanks for your help in
advance.

Frederick Chow
Hong Kong.