Hi everyone


I tried to write code which should do actions like:
1. If user try to close file its should ask if the file should be
saved.
- If NO file should be closed immediately without any prompts (like "Do
You want to save the file")
- If YES it should be saved and after saved again but as csv file.

My problem is that to avoid "Do You want to save the file" which appear
automatically when file is being closed with some changes done I use
ActiveWorkBook.Close(False). But if I use it with procedure it does
something like restart of the procedure (I tried to put this code in
different places and its always the same)

I looks lik this. Procedure is asking "Save the file?"
User choose "NO" and the procedure asks again "Save the file?" After
repeating this question it goes on but I don't know why it repeats it

Below is the code. If it can be done more cleary show me how please.

Complete another question Is there a way to avoid questions:
"Do you want to overwrite the 1.csv file"
and
"Do you want to save the changes in csv file"

I tried without success.

Thanks
PAT

Procedu

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error GoTo ErrorHandler
Dim Msg, Style, Title, Help, Ctxt, Response, MyString, NoMsg, NoStyle,
NoTitle, NoResponse, ErrMsg, ErrStyle, ErrTitle, ErrResponse,
SavResponse, SavMsg

SavMsg = "Do you want to save file?"

Msg = "Do you want to save file as csv?"
Style = vbYesNo + vbDefaultButton1
Title = "Saving csv"

NoMsg = "Data is not saved , YES - Return, NO - Close file"
NoStyle = vbYesNo + vbCritical + vbDefaultButton1
NoTitle = "Saving"

ErrMsg = "Error apeared. Probably you doesn't confirm saving csv file.
You will return to the file"
ErrStyle = vbYes + vbCritical
ErrTitle = "ERROR"



SavResponse = MsgBox(SavMsg, Style)
If SavResponse = vbNo Then
ActiveWorkbook.Close (False)
Exit Sub
Else
ActiveWorkbook.Save
End If

Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then

ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\1.csv", FileFormat:= _
xlCSV, CreateBackup:=False

Else

NoResponse = MsgBox(NoMsg, NoStyle, NoTitle)
If NoResponse = vbYes Then
Cancel = True

End If
End If

Exit Sub
ErrorHandler:
ErrResonse = MsgBox(ErrMsg, ErrStyle, ErrTitle)
Cancel = True



End Sub