Is their a way to display a message box, when the user click to exit
the worksheet using a macro?

The user will have 3 options Yes, No, Cancel.

How can I program this using VBA in Ms Excel XP?

------------------------------------------------

The code I use at the moment saves the worksheet without asking the
user:

Sub CloseSpreedsheet()
'Saves spreedsheet
ThisWorkbook.Saved = True
'Closes the application
Application.Quit
End Sub

Olly,

First of all, the line of code
ThisWorkbook.Saved = True
does NOT save the workbook. It tells Excel that the workbook does not need
to be saved, but does NOT actually save any changes.

Try something like the following:

Sub CloseSpreedsheet()
Dim Res As Long
Res = MsgBox("Do you want to save?", vbYesNoCancel)
Select Case Res
Case vbYes
ThisWorkbook.Save
Case vbNo
ThisWorkbook.Saved = True
Case vbCancel
Exit Sub
End Select
Application.Quit
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Olly" wrote in message
om...
Is their a way to display a message box, when the user click to exit
the worksheet using a macro?

The user will have 3 options Yes, No, Cancel.

How can I program this using VBA in Ms Excel XP?

------------------------------------------------

The code I use at the moment saves the worksheet without asking the
user:

Sub CloseSpreedsheet()
'Saves spreedsheet
ThisWorkbook.Saved = True
'Closes the application
Application.Quit
End Sub

If you want to save the workbook automatically then the
code is
ThisWorkbook.Save

On Excel 97 at least, is the default to ask users if they
want to save changes before closing a workbook.

If you want to produce this message box yourself, you
could try

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Select Case MsgBox("Want to SAve", vbQuestion
+YesNoCancel)
Case vbYes
ThisWorkbook.Save
Case vbNo
ThisWorkbook.Saved = True
Case vbCancel
Cancel = True
End Select
End Sub


libby

-----Original Message-----
Olly,

First of all, the line of code
ThisWorkbook.Saved = True
does NOT save the workbook. It tells Excel that the
workbook does not need
to be saved, but does NOT actually save any changes.

Try something like the following:

Sub CloseSpreedsheet()
Dim Res As Long
Res = MsgBox("Do you want to save?", vbYesNoCancel)
Select Case Res
Case vbYes
ThisWorkbook.Save
Case vbNo
ThisWorkbook.Saved = True
Case vbCancel
Exit Sub
End Select
Application.Quit
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Olly" wrote in message
. com...
Is their a way to display a message box, when the user
click to exit
the worksheet using a macro?

The user will have 3 options Yes, No, Cancel.

How can I program this using VBA in Ms Excel XP?

------------------------------------------------

The code I use at the moment saves the worksheet
without asking the
user:

Sub CloseSpreedsheet()
'Saves spreedsheet
ThisWorkbook.Saved = True
'Closes the application
Application.Quit
End Sub


.

Thanks that's what i needed

"Chip Pearson" wrote in message ...
Olly,

First of all, the line of code
ThisWorkbook.Saved = True
does NOT save the workbook. It tells Excel that the workbook does not need
to be saved, but does NOT actually save any changes.

Try something like the following:

Sub CloseSpreedsheet()
Dim Res As Long
Res = MsgBox("Do you want to save?", vbYesNoCancel)
Select Case Res
Case vbYes
ThisWorkbook.Save
Case vbNo
ThisWorkbook.Saved = True
Case vbCancel
Exit Sub
End Select
Application.Quit
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com