Before Close event

[Guest]

I'm adding a macro in the Workbook_BeforeClose module to check a specific
sheet in the workbook for data. If there is data, a msgbox is displayed with
a Yes, No, Cancel button.

If the user clicks, "yes", then it will clear the sheet and close the workbook

If the user clicks, "no" it will send the data to a database and close the
workbook

If the user clicks, "cancel", I want the close to stop so the user can
review the data.

I have tried the following code but the workbook still closes.

If MyAnswer = 2 Then End

Any suggestions on how to stop the close process would be greatly
appreciated. Thanks for the help.

 

[okrob]

I'm adding a macro in the Workbook_BeforeClose module to check a specific
sheet in the workbook for data. If there is data, a msgbox is displayed with
a Yes, No, Cancel button.

If the user clicks, "yes", then it will clear the sheet and close the workbook

If the user clicks, "no" it will send the data to a database and close the
workbook

If the user clicks, "cancel", I want the close to stop so the user can
review the data.

I have tried the following code but the workbook still closes.

If MyAnswer = 2 Then End

Any suggestions on how to stop the close process would be greatly
appreciated. Thanks for the help.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

under your sub, if the vbanswer is cancel, then set Cancel = True

This should stop your workbook from closing...
Rob

 

[Guest]

Instead of this line

If MyAnswer = 2 Then End

Use this

If MyAnswer = 2 Then Cancel=True