auto close - give user the option to keep file open
Posted by JAF on November 29, 1999 6:41 AM
I have an existing auto close macro which displays a message box when the spreadsheet has been incorrectly completed, but when the user clicks the OK button, the file closes automatically.
Is it possible to set up an auto close type event to display the error but also to give the user the option of either closing the file or keeping it open in order for them to correct the error.
Any assistance greatly appreciated.
JAF.
Posted by Chris on November 29, 1999 7:37 AM
JAF,
Try this:
http://www.j-walk.com/ss/excel/tips/tip78.htm
HTH,
Chris
Posted by JAF on November 30, 1999 2:50 AM
Partially Successful
This works to a point, but I don't seem to be able to keep the file open.
What I need is the option for the user to either close the file (and hopefully correct the error later!) or to keep it open in order to correct the errors there and then.
Using the Workbook_BeforeClose option allows me to perform certain actions, but the file still closes irrespective of which button I press.
Posted by Ivan Moala on December 01, 1999 12:45 AM
JAF
You are probably missing the cancel as Boolean
setting, setting this to true stops the workbook from closing.
Try something like this;
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Ans As Integer
If Range("A1") <> True Then
Ans = MsgBox("Errors in workbook" & Chr(13) & "Check sheet ?", vbYesNo)
If Ans = vbYes Then
Cancel = True
End If
End If
End Sub
This assumes that a certain range = A1 is
checked to see if the sheet has errors
HTH
Ivan
