I would use the BeforeSave and BeforeClose events in
conjunction with each other. Here's an example:

'//Constructive criticism from VBA programmers appreciated

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As _
Boolean, Cancel As Boolean)
Dim checkRng As Range
Set checkRng = Sheets("Sheet1").Range("A1")
If IsError(checkRng) Then
Cancel = True
MsgBox "Error found in " & _
checkRng.Address(False, False) & "."
End If
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Me.Saved = False Then
MsgBox "Please save before closing."
Cancel = True
End If
End Sub


--
Both macros should be placed in the ThisWorbook module.

HTH
Jason
Atlanta, GA

-----Original Message-----

Hi

I have to validate the data at the time of closing the
sheet. If some errors are present in the data, I need to
throw a message and I should not close the sheet.

I am using the following code to achieve it. Here the
problem is I can throw an error message if the data is
wrong but how to stop the work book from closing. I have
to give a chanse to user to correct the data. How this
can be achieved?

Private Sub Workbook_BeforeClose(Cancel As Boolean)

{ Validate the data }

If Error Msgbox ( " Error .... Correct the Data " )

End Sub

--
Message posted via http://www.officekb.com
.