Hi
The problem with the last approach is if the user answers vbyes then the
msgbox will be presented a second time.
In addition - if a change has been made to the workbook then the Excel Save
dialog will also be presented.
As the post seems to be suggesting his own alternative to the Excel Save
dialog then one way is:
Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim abcResponse1 As Long
Dim cbsState As Boolean

'''initial property state
cbsState = Application.CalculateBeforeSave

abcResponse1 = MsgBox("Do you want to Save the file?", vbYesNo)

If abcResponse1 = vbYes Then
With Application
'''turn off to prevent "Save" dialog
.CalculateBeforeSave = False
'''save data
ActiveWorkbook.Save
'''restore to original
.CalculateBeforeSave = cbsState
'''save restored properties
ActiveWorkbook.Save
End With
ElseIf abcResponse1 = vbNo Then
ActiveWorkbook.Saved = True
Else
Cancel = True
End If

'''other stuff

End Sub

hth

Geoff


"Office_Novice" wrote:

Try this...
Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim abcResponse1
abcResponse1 = MsgBox("Do you want to Save the file?", vbYesNo)

If abcResponse1 = vbYes Then
ActiveWorkbook.Close SaveChanges:=True, Filename:="MyWorkbookName.xls"
If abcResponse1 = vbNo Then
ActiveWorkbook.Close SaveChanges:=False
End If
End If
End Sub


"Raj" wrote:

Hi,

In the Before close event of the workbook I have the following code:

If ThisWorkbook.Saved = False Then abcresponse1 = MsgBox("Do you want
to Save the file?", vbYesNo)

What should be the code for abcresponse1 = vbNo so that all changes
made to the workbook since it was last saved are abandoned and the
workbook is saved in that state?

The workbook before close event has code further down the line that
has to be processed.

Thanks in advance for the help.

Regards,
Raj