[SOLVED] Auto-save on close

In Excel 2000 is it possible to imbed some code in a worksheet that will
automatically save the file when it is closed (rather than go through the
"are you sure...." routine). The standard autosave add-in is already being
used but the user wants to be able to close the sheet and not be prompted -
just have the file saved as default.

TIA

IK

Hi
u can use this code.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Save
End Sub

"SthOzNewbie" wrote:

> In Excel 2000 is it possible to imbed some code in a worksheet that will
> automatically save the file when it is closed (rather than go through the
> "are you sure...." routine). The standard autosave add-in is already being
> used but the user wants to be able to close the sheet and not be prompted -
> just have the file saved as default.
>
> TIA
>
> IK

Thanks for the quick reply.

I saved this code into a module in a workbook but was still prompted with
the "Do you want to save changes..." dialogue when I closed the workbook.
I have not worked with code much so I have probably done something very
basically wrong.

Regards,

Ilya


"Muhammed Rafeek M" wrote:

> Hi
> u can use this code.
>
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> ThisWorkbook.Save
> End Sub
>
> "SthOzNewbie" wrote:
>
> > In Excel 2000 is it possible to imbed some code in a worksheet that will
> > automatically save the file when it is closed (rather than go through the
> > "are you sure...." routine). The standard autosave add-in is already being
> > used but the user wants to be able to close the sheet and not be prompted -
> > just have the file saved as default.
> >
> > TIA
> >
> > IK

You need to add a line:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Save
Cancel=True
End Sub

NickHK

"SthOzNewbie" <SthOzNewbie@discussions.microsoft.com> wrote in message
news:64E3BF7B-1425-425E-AD3E-3FF80A2CB346@microsoft.com...
> Thanks for the quick reply.
>
> I saved this code into a module in a workbook but was still prompted with
> the "Do you want to save changes..." dialogue when I closed the workbook.
> I have not worked with code much so I have probably done something very
> basically wrong.
>
> Regards,
>
> Ilya
>
>
> "Muhammed Rafeek M" wrote:
>
> > Hi
> > u can use this code.
> >
> > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > ThisWorkbook.Save
> > End Sub
> >
> > "SthOzNewbie" wrote:
> >
> > > In Excel 2000 is it possible to imbed some code in a worksheet that
will
> > > automatically save the file when it is closed (rather than go through
the
> > > "are you sure...." routine). The standard autosave add-in is already
being
> > > used but the user wants to be able to close the sheet and not be
prompted -
> > > just have the file saved as default.
> > >
> > > TIA
> > >
> > > IK

Thank you. I added the line but am still getting the message box coming up.

This is probably a really dumb question but should I be inserting the actual
name of the workbook somewhere ?

Have I put the code in the right place ? (It's in Module1 of the workbook)

Regards,

IK

"NickHK" wrote:

> You need to add a line:
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> ThisWorkbook.Save
> Cancel=True
> End Sub
>
> NickHK
>
> "SthOzNewbie" <SthOzNewbie@discussions.microsoft.com> wrote in message
> news:64E3BF7B-1425-425E-AD3E-3FF80A2CB346@microsoft.com...
> > Thanks for the quick reply.
> >
> > I saved this code into a module in a workbook but was still prompted with
> > the "Do you want to save changes..." dialogue when I closed the workbook.
> > I have not worked with code much so I have probably done something very
> > basically wrong.
> >
> > Regards,
> >
> > Ilya
> >
> >
> > "Muhammed Rafeek M" wrote:
> >
> > > Hi
> > > u can use this code.
> > >
> > > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > > ThisWorkbook.Save
> > > End Sub
> > >
> > > "SthOzNewbie" wrote:
> > >
> > > > In Excel 2000 is it possible to imbed some code in a worksheet that
> will
> > > > automatically save the file when it is closed (rather than go through
> the
> > > > "are you sure...." routine). The standard autosave add-in is already
> being
> > > > used but the user wants to be able to close the sheet and not be
> prompted -
> > > > just have the file saved as default.
> > > >
> > > > TIA
> > > >
> > > > IK
>
>
>

No, it is a workbook events "Workbook_BeforeClose", so it needs to go on the
ThisWorkBook module.
Sorry, delete the "Cancel=True", thought we were in the _BeforeSave event

NickHK

"SthOzNewbie" <SthOzNewbie@discussions.microsoft.com> wrote in message
news:E5C1A1C6-4A00-432C-A0C1-3861CA89FBB5@microsoft.com...
> Thank you. I added the line but am still getting the message box coming
up.
>
> This is probably a really dumb question but should I be inserting the
actual
> name of the workbook somewhere ?
>
> Have I put the code in the right place ? (It's in Module1 of the workbook)
>
> Regards,
>
> IK
>
> "NickHK" wrote:
>
> > You need to add a line:
> > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > ThisWorkbook.Save
> > Cancel=True
> > End Sub
> >
> > NickHK
> >
> > "SthOzNewbie" <SthOzNewbie@discussions.microsoft.com> wrote in message
> > news:64E3BF7B-1425-425E-AD3E-3FF80A2CB346@microsoft.com...
> > > Thanks for the quick reply.
> > >
> > > I saved this code into a module in a workbook but was still prompted
with
> > > the "Do you want to save changes..." dialogue when I closed the
workbook.
> > > I have not worked with code much so I have probably done something
very
> > > basically wrong.
> > >
> > > Regards,
> > >
> > > Ilya
> > >
> > >
> > > "Muhammed Rafeek M" wrote:
> > >
> > > > Hi
> > > > u can use this code.
> > > >
> > > > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > > > ThisWorkbook.Save
> > > > End Sub
> > > >
> > > > "SthOzNewbie" wrote:
> > > >
> > > > > In Excel 2000 is it possible to imbed some code in a worksheet
that
> > will
> > > > > automatically save the file when it is closed (rather than go
through
> > the
> > > > > "are you sure...." routine). The standard autosave add-in is
already
> > being
> > > > > used but the user wants to be able to close the sheet and not be
> > prompted -
> > > > > just have the file saved as default.
> > > > >
> > > > > TIA
> > > > >
> > > > > IK
> >
> >
> >

Excellent, that's done the trick

Cheers,

IK

"NickHK" wrote:

> No, it is a workbook events "Workbook_BeforeClose", so it needs to go on the
> ThisWorkBook module.
> Sorry, delete the "Cancel=True", thought we were in the _BeforeSave event
>
> NickHK
>
> "SthOzNewbie" <SthOzNewbie@discussions.microsoft.com> wrote in message
> news:E5C1A1C6-4A00-432C-A0C1-3861CA89FBB5@microsoft.com...
> > Thank you. I added the line but am still getting the message box coming
> up.
> >
> > This is probably a really dumb question but should I be inserting the
> actual
> > name of the workbook somewhere ?
> >
> > Have I put the code in the right place ? (It's in Module1 of the workbook)
> >
> > Regards,
> >
> > IK
> >
> > "NickHK" wrote:
> >
> > > You need to add a line:
> > > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > > ThisWorkbook.Save
> > > Cancel=True
> > > End Sub
> > >
> > > NickHK
> > >
> > > "SthOzNewbie" <SthOzNewbie@discussions.microsoft.com> wrote in message
> > > news:64E3BF7B-1425-425E-AD3E-3FF80A2CB346@microsoft.com...
> > > > Thanks for the quick reply.
> > > >
> > > > I saved this code into a module in a workbook but was still prompted
> with
> > > > the "Do you want to save changes..." dialogue when I closed the
> workbook.
> > > > I have not worked with code much so I have probably done something
> very
> > > > basically wrong.
> > > >
> > > > Regards,
> > > >
> > > > Ilya
> > > >
> > > >
> > > > "Muhammed Rafeek M" wrote:
> > > >
> > > > > Hi
> > > > > u can use this code.
> > > > >
> > > > > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > > > > ThisWorkbook.Save
> > > > > End Sub
> > > > >
> > > > > "SthOzNewbie" wrote:
> > > > >
> > > > > > In Excel 2000 is it possible to imbed some code in a worksheet
> that
> > > will
> > > > > > automatically save the file when it is closed (rather than go
> through
> > > the
> > > > > > "are you sure...." routine). The standard autosave add-in is
> already
> > > being
> > > > > > used but the user wants to be able to close the sheet and not be
> > > prompted -
> > > > > > just have the file saved as default.
> > > > > >
> > > > > > TIA
> > > > > >
> > > > > > IK
> > >
> > >
> > >
>
>
>

Don't do this.

You're gonna be upset when you make a disastrous error -- deleting data or
changing code -- and then want to close without saving.

If you do do it, make sure you keep plenty of backups.

SthOzNewbie wrote:
>
> In Excel 2000 is it possible to imbed some code in a worksheet that will
> automatically save the file when it is closed (rather than go through the
> "are you sure...." routine). The standard autosave add-in is already being
> used but the user wants to be able to close the sheet and not be prompted -
> just have the file saved as default.
>
> TIA
>
> IK

--

Dave Peterson