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.

try using

Application.DisplayAlerts = false

and

Wb.saved = true
wb.close false


HTH
Philip





"Frederick Chow" wrote:

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.

Thanks for your help, but:

If Wb.Saved = True is inserted, even though the user clicked "Cancel' in
response to asking for replacement, then the file will closed, where the
expected behaviour should be the file remains open.

If Application.DisplayAlerts = False is inserted, then the user will not
have a chance to cancel the choice, which is less than desired.

So any other suggestions?

Frederick Chow
Hong Kong.

"Philip" wrote in message
...
try using

Application.DisplayAlerts = false

and

Wb.saved = true
wb.close false


HTH
Philip





"Frederick Chow" wrote:

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.

Hi Phillip,

Finally I have sought out a way to solve the problem: Add "If Wb.Saved =
False then Exit Sub" statement immediately after the Call ActualSave
command.

Anyway thanks for your attention.

Frederick Chow
Hong Kong.

"Philip" wrote in message
...
try using

Application.DisplayAlerts = false

and

Wb.saved = true
wb.close false


HTH
Philip





"Frederick Chow" wrote:

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.