Hold closing the sheet...

[Sridhar Pentlavalli via OfficeKB.com]

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

 

[Nick Hodge]

Sridhar

Set the Cancel Boolean to True and the workbook will not close

Private Sub Workbook_BeforeClose(Cancel As Boolean)

{ Validate the data }

If Error Msgbox ( " Error .... Correct the Data " )
Cancel = True

End Sub


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Jason Morin]

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?