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.
--
JT

On Apr 5, 10:06 am, JT wrote:
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.
--
JT

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

Instead of this line

If MyAnswer = 2 Then End

Use this

If MyAnswer = 2 Then Cancel=True


--
Hope that helps.

Vergel Adriano


"JT" wrote:

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.
--
JT