I'm trying to complete a Workbook.BeforeClose event but am having an issue
with it.

When the user closes the workbook, there is code that checks for data on a
certain worksheet. If it finds data, I display a message box with 3 buttons
(Yes, No, Cancel) that tells the user what will happen with each selection.

If the user pushes "Yes", the workbook should close without saving any data

If the user pushes "No", the code should do some stuff to the data and then
close without saving any data.

If the user pushes, "Cancel", the code stops, the workbook stays open and
the user can review the data.

There is no issue with the "Cancel" protion. I'm having trouble with the
"Yes" and "No" portions. I can get the code to do everything else thing I
want it to except an Excel message box is automatically displayed at the end
of the module. The message says "Do you want to save changes......" with
"yes", "no", and "cancel" buttons.

I would prefer to prevent this message from being displayed or my second
choice is to automatically program it to push the "no" button.

Is there a way to prevent this message and close the workbook without saving
the data.

Thanks for the help. This is a terrific resource for issues. Thanks again..

--
JT

Set the workbook Saved property to True.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"JT" wrote in message
...
I'm trying to complete a Workbook.BeforeClose event but am having an issue
with it.

When the user closes the workbook, there is code that checks for data on a
certain worksheet. If it finds data, I display a message box with 3
buttons
(Yes, No, Cancel) that tells the user what will happen with each
selection.

If the user pushes "Yes", the workbook should close without saving any
data

If the user pushes "No", the code should do some stuff to the data and
then
close without saving any data.

If the user pushes, "Cancel", the code stops, the workbook stays open and
the user can review the data.

There is no issue with the "Cancel" protion. I'm having trouble with the
"Yes" and "No" portions. I can get the code to do everything else thing
I
want it to except an Excel message box is automatically displayed at the
end
of the module. The message says "Do you want to save changes......" with
"yes", "no", and "cancel" buttons.

I would prefer to prevent this message from being displayed or my second
choice is to automatically program it to push the "no" button.

Is there a way to prevent this message and close the workbook without
saving
the data.

Thanks for the help. This is a terrific resource for issues. Thanks
again..

--
JT

Before your macro terminates try adding

ThisWorkbook.Saved = True

This should tell Excel that the workbook does not need to be saved.

That said, I believe at least one version of excel had a bug associated with
this, but I don't recall which - hopefully it works for you.

--
Regards,
Tom Ogilvy

"JT" wrote:

I'm trying to complete a Workbook.BeforeClose event but am having an issue
with it.

When the user closes the workbook, there is code that checks for data on a
certain worksheet. If it finds data, I display a message box with 3 buttons
(Yes, No, Cancel) that tells the user what will happen with each selection.

If the user pushes "Yes", the workbook should close without saving any data

If the user pushes "No", the code should do some stuff to the data and then
close without saving any data.

If the user pushes, "Cancel", the code stops, the workbook stays open and
the user can review the data.

There is no issue with the "Cancel" protion. I'm having trouble with the
"Yes" and "No" portions. I can get the code to do everything else thing I
want it to except an Excel message box is automatically displayed at the end
of the module. The message says "Do you want to save changes......" with
"yes", "no", and "cancel" buttons.

I would prefer to prevent this message from being displayed or my second
choice is to automatically program it to push the "no" button.

Is there a way to prevent this message and close the workbook without saving
the data.

Thanks for the help. This is a terrific resource for issues. Thanks again..

--
JT