How do I prevent someone from closing a workbook unless
changes have been saved. I want to force the user to save
a file and not allow them to exit unless the changes have
been saved.

I have an Excel Workbook that the user will open as a
template. They will enter date, then save the information
and quit. I am using forms to control the users interface
but if they close the form, they can click the X at the
top (or select Quit from the Menu) and exit, then click
cancel on the SaveAs dialog box and quit the application
without saving changes.

I use a Form to allow the user to enter a file name, then
place the file in a specified directory. This all works
fine, but if they simply close the form and then hit the X
at the top, they can quit the application without saving
changes. If I can force them to go back to the form where
they enter a file name, that would be prefered because I
can then place the file in a directory of my choosing.

Any ideas or help would be appreciated.

Kevin

Hi Kevin,

What about the following in this workbook code module?

Private Sub Workbook_beforeclose(Cancel As Boolean)
Application.ScreenUpdating = False

If ThisWorkbook.Saved = False Then

MsgBox "Please save the workbook before exiting"
Cancel = True

End If

End Sub

Regards,
Hari
India

"Kevin" wrote in message
...
How do I prevent someone from closing a workbook unless
changes have been saved. I want to force the user to save
a file and not allow them to exit unless the changes have
been saved.

I have an Excel Workbook that the user will open as a
template. They will enter date, then save the information
and quit. I am using forms to control the users interface
but if they close the form, they can click the X at the
top (or select Quit from the Menu) and exit, then click
cancel on the SaveAs dialog box and quit the application
without saving changes.

I use a Form to allow the user to enter a file name, then
place the file in a specified directory. This all works
fine, but if they simply close the form and then hit the X
at the top, they can quit the application without saving
changes. If I can force them to go back to the form where
they enter a file name, that would be prefered because I
can then place the file in a directory of my choosing.

Any ideas or help would be appreciated.

Kevin

Hi Kevin

Don't. People quit wizards for a reason. If you, as a developer, really
believe that all changes are for the better, then save by code without
annoying the user. If not then trust their decisions.

HTH. Best wishes Harald

"Kevin" skrev i melding
...
How do I prevent someone from closing a workbook unless
changes have been saved. I want to force the user to save
a file and not allow them to exit unless the changes have
been saved.

Thanks for the help!
-----Original Message-----
How do I prevent someone from closing a workbook unless
changes have been saved. I want to force the user to save
a file and not allow them to exit unless the changes have
been saved.

I have an Excel Workbook that the user will open as a
template. They will enter date, then save the information
and quit. I am using forms to control the users interface
but if they close the form, they can click the X at the
top (or select Quit from the Menu) and exit, then click
cancel on the SaveAs dialog box and quit the application
without saving changes.

I use a Form to allow the user to enter a file name, then
place the file in a specified directory. This all works
fine, but if they simply close the form and then hit the
X
at the top, they can quit the application without saving
changes. If I can force them to go back to the form where
they enter a file name, that would be prefered because I
can then place the file in a directory of my choosing.

Any ideas or help would be appreciated.

Kevin
.

Hi Harald,

May be I shouldnt have responded in light of my knowing not much. (dont know
what and how of Forms etc).

People quit wizards for a reason.
In a code (I know and use only simple IF, For etc) I have, I fear that
people might click on the close button by mistake and might lose the
important or laborious changes they have done. Hence to tread a middle path
between "changes are for the better, then save by code" and "If not then
trust their decisions." I use the close event with cancel as boolean so that
if the user clicks on close by mistake and if they think the changes are
legitimate then they may save and not exit out of the workbook. But suppose
the changes were not legitimate and then they may temporarily save it (lets
say in a temp folder) and then delete it. Is my logic correct?

Regards,
Hari
India

"Harald Staff" wrote in message
...
Hi Kevin

Don't. People quit wizards for a reason. If you, as a developer, really
believe that all changes are for the better, then save by code without
annoying the user. If not then trust their decisions.

HTH. Best wishes Harald

"Kevin" skrev i melding
...
How do I prevent someone from closing a workbook unless
changes have been saved. I want to force the user to save
a file and not allow them to exit unless the changes have
been saved.

Hi Hari

No criticism intended. Your code is ok and your logic is fine. But Excel
herself will stop and ask "do you want to save changes ? (yes / no /
cancel)" if the workbook is dirty (unsaved) upon close. Which in theory
should deal with all your concerns on this.

I believe users should always have an emergency exit out of any application
or situation; properly marked, safe, friendly and understandable.

In the initial post Kevin says "They will enter date, then save the
information and quit". They will usually not. Many will open the file
because they opened the wrong one, others because they thought this was what
they needed but it's obviously not, still others will want to quit unsaved
because they want to do this later instead, it's too much of a job to do it
properly. Then we, developers, tell them "You are not allowed to leave this
room until you finish the job", or "I replaced the original data with what
you wrote anyway", or, preferrably, "Ok, nothing saved, welcome back when
you're ready".

My logic may not be correct either, but worst case scenarios are helpful to
me when I design user interfaces :-)

Best wishes Harald

"Hari" skrev i melding
...
May be I shouldnt have responded in light of my knowing not much. (dont
know
what and how of Forms etc).
People quit wizards for a reason.
In a code (I know and use only simple IF, For etc) I have, I fear that
people might click on the close button by mistake and might lose the
important or laborious changes they have done. Hence to tread a middle
path
between "changes are for the better, then save by code" and "If not then
trust their decisions." I use the close event with cancel as boolean so
that
if the user clicks on close by mistake and if they think the changes are
legitimate then they may save and not exit out of the workbook. But
suppose
the changes were not legitimate and then they may temporarily save it
(lets
say in a temp folder) and then delete it. Is my logic correct?

Regards,
Hari
India

"Harald Staff" wrote in message
...
Hi Kevin

Don't. People quit wizards for a reason. If you, as a developer, really
believe that all changes are for the better, then save by code without
annoying the user. If not then trust their decisions.

HTH. Best wishes Harald

"Kevin" skrev i melding
...
How do I prevent someone from closing a workbook unless
changes have been saved. I want to force the user to save
a file and not allow them to exit unless the changes have
been saved.