Hi,

I'm trying to create a Yes No button that activates when the user tries to
close excel, what i want is that if the user selects yes for the workbook to
save and close. If they click No I want them to be able to go back to the
spreadsheet and carry on working. At the moment all i have got is the yes
bit, when they click no the "Do you want to save changes" box appears and i
can't for the life of me get the macro to select cancel.
Here is the code i'm working on at the minute.
Sub Auto_Close()
'
' Auto_Close Macro
' Macro recorded 09/11/2007 by sparrett
'

Application.DisplayAlerts = False
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Have You Saved A copy?"
Style = vbYesNo + vbInformation
Title = "Clear Down Button"
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
' context.
' Display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.
MyString = "Yes" ' Perform some action


Else ' User chose No.
MyString = "No" ' Perform some action.

Application.DisplayAlerts = False
ThisWorkbook.Saved = False


End If
End Sub

Any help would be appreciated.

Cheers

Simon