close excel without saving

I have inserted various code(s) in vbs "this workbook" using private sub
auto_close
and private sub work_book close() after other code to close excel/workbook
without saving or displaying a prompt but none seems to work.
I have used 'application.displayalerts = false'.' application.quit',
'thisworkbook.close savechanges:=false' etc etc in a variety of different
combinations.
Perhaps I am placing the code in the wrong place or perhaps I should be
saving before quitting or ..... ?
Can anyone help ?
Mike

in the ThisWorkbook module

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Saved = True
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
cancel = True
End Sub

After you add the code, you will have to disable macros manually, then save
the workbook.
--
Regards,
Tom Ogilvy


"Mike" <[email protected]> wrote in message
news:[email protected]...
> I have inserted various code(s) in vbs "this workbook" using private sub
> auto_close
> and private sub work_book close() after other code to close excel/workbook
> without saving or displaying a prompt but none seems to work.
> I have used 'application.displayalerts = false'.' application.quit',
> 'thisworkbook.close savechanges:=false' etc etc in a variety of different
> combinations.
> Perhaps I am placing the code in the wrong place or perhaps I should be
> saving before quitting or ..... ?
> Can anyone help ?
> Mike

Tom,
Thanks for that. I haven't tried it yet 'coz I didn't understand the
'disable macros first' comment and the workbook contains loads of macros
which would take for ever to disable. I am wanting other users not to be able
to save changes when using the excel 'X' 's (quit buttons) nor to see a save
prompt.
Mike

"Tom Ogilvy" wrote:

> in the ThisWorkbook module
>
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> ThisWorkbook.Saved = True
> End Sub
> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> Boolean)
> cancel = True
> End Sub
>
> After you add the code, you will have to disable macros manually, then save
> the workbook.
> --
> Regards,
> Tom Ogilvy
>
>
> "Mike" <[email protected]> wrote in message
> news:[email protected]...
> > I have inserted various code(s) in vbs "this workbook" using private sub
> > auto_close
> > and private sub work_book close() after other code to close excel/workbook
> > without saving or displaying a prompt but none seems to work.
> > I have used 'application.displayalerts = false'.' application.quit',
> > 'thisworkbook.close savechanges:=false' etc etc in a variety of different
> > combinations.
> > Perhaps I am placing the code in the wrong place or perhaps I should be
> > saving before quitting or ..... ?
> > Can anyone help ?
> > Mike
>
>
>

Tom Ogilvy wrote:
> in the ThisWorkbook module
>
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> ThisWorkbook.Saved = True
> End Sub
> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> Boolean)
> cancel = True
> End Sub
>
> After you add the code, you will have to disable macros manually,
> then save the workbook.

When I need to do this I create a sub called AdminSave which disables
events, saves, then enables events again and usually map Ctrl+Shift+S to run
it. This way I can save my changes but users changes won't be saved.
It's also handy if you're going to be using lots of on event code to make a
macro that toggles events and assign a keystroke combo to it.


>
> "Mike" <[email protected]> wrote in message
> news:[email protected]...
>> I have inserted various code(s) in vbs "this workbook" using
>> private sub auto_close
>> and private sub work_book close() after other code to close
>> excel/workbook without saving or displaying a prompt but none seems
>> to work.
>> I have used 'application.displayalerts = false'.' application.quit',
>> 'thisworkbook.close savechanges:=false' etc etc in a variety of
>> different combinations.
>> Perhaps I am placing the code in the wrong place or perhaps I should
>> be saving before quitting or ..... ?
>> Can anyone help ?
>> Mike

Mike wrote:
> Tom,
> Thanks for that. I haven't tried it yet 'coz I didn't understand the
> 'disable macros first' comment and the workbook contains loads of
> macros which would take for ever to disable. I am wanting other users
> not to be able to save changes when using the excel 'X' 's (quit
> buttons) nor to see a save prompt.
> Mike

you only need to disable events:

application.enableevents = false

this will stop the on event code from running (like Workbook and Worksheet
code) until you do a true

>
> "Tom Ogilvy" wrote:
>
>> in the ThisWorkbook module
>>
>> Private Sub Workbook_BeforeClose(Cancel As Boolean)
>> ThisWorkbook.Saved = True
>> End Sub
>> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
>> Boolean)
>> cancel = True
>> End Sub
>>
>> After you add the code, you will have to disable macros manually,
>> then save the workbook.
>> --
>> Regards,
>> Tom Ogilvy
>>
>>
>> "Mike" <[email protected]> wrote in message
>> news:[email protected]...
>>> I have inserted various code(s) in vbs "this workbook" using
>>> private sub auto_close
>>> and private sub work_book close() after other code to close
>>> excel/workbook without saving or displaying a prompt but none seems
>>> to work.
>>> I have used 'application.displayalerts = false'.' application.quit',
>>> 'thisworkbook.close savechanges:=false' etc etc in a variety of
>>> different combinations.
>>> Perhaps I am placing the code in the wrong place or perhaps I
>>> should be saving before quitting or ..... ?
>>> Can anyone help ?
>>> Mike

Tom,
Me again.
Have copied/pasted your code in but the second private sub is showing in red
which indicates something is quite right.
Mike

"Tom Ogilvy" wrote:

> in the ThisWorkbook module
>
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> ThisWorkbook.Saved = True
> End Sub
> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> Boolean)
> cancel = True
> End Sub
>
> After you add the code, you will have to disable macros manually, then save
> the workbook.
> --
> Regards,
> Tom Ogilvy
>
>
> "Mike" <[email protected]> wrote in message
> news:[email protected]...
> > I have inserted various code(s) in vbs "this workbook" using private sub
> > auto_close
> > and private sub work_book close() after other code to close excel/workbook
> > without saving or displaying a prompt but none seems to work.
> > I have used 'application.displayalerts = false'.' application.quit',
> > 'thisworkbook.close savechanges:=false' etc etc in a variety of different
> > combinations.
> > Perhaps I am placing the code in the wrong place or perhaps I should be
> > saving before quitting or ..... ?
> > Can anyone help ?
> > Mike
>
>
>

If the code prevents saving the workbook, then how are you going to save the
workbook after you enter the code.

What you have to do is go to the immediate window and enter

Application.EnableEvents = False

now save the workbook and close it.

Go to the immediate window and enter

Application.EnableEvents = True

to reenable events.

--
Regards,
Tom Ogilvy

"Mike" <[email protected]> wrote in message
news:[email protected]...
> Tom,
> Thanks for that. I haven't tried it yet 'coz I didn't understand the
> 'disable macros first' comment and the workbook contains loads of macros
> which would take for ever to disable. I am wanting other users not to be
able
> to save changes when using the excel 'X' 's (quit buttons) nor to see a
save
> prompt.
> Mike
>
> "Tom Ogilvy" wrote:
>
> > in the ThisWorkbook module
> >
> > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > ThisWorkbook.Saved = True
> > End Sub
> > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> > Boolean)
> > cancel = True
> > End Sub
> >
> > After you add the code, you will have to disable macros manually, then
save
> > the workbook.
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Mike" <[email protected]> wrote in message
> > news:[email protected]...
> > > I have inserted various code(s) in vbs "this workbook" using private
sub
> > > auto_close
> > > and private sub work_book close() after other code to close
excel/workbook
> > > without saving or displaying a prompt but none seems to work.
> > > I have used 'application.displayalerts = false'.' application.quit',
> > > 'thisworkbook.close savechanges:=false' etc etc in a variety of
different
> > > combinations.
> > > Perhaps I am placing the code in the wrong place or perhaps I should
be
> > > saving before quitting or ..... ?
> > > Can anyone help ?
> > > Mike
> >
> >
> >

Since I copied it out of a workbook, it is right. Perhaps you didn't
correct for wordwrap.

here is one adjusted so you shouldn't have a wordwrap problem

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Saved = True
End Sub

Private Sub Workbook_BeforeSave( _
ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Cancel = True
End Sub

--
Regards,
Tom Ogilvy



"Mike" <[email protected]> wrote in message
news:[email protected]...
> Tom,
> Me again.
> Have copied/pasted your code in but the second private sub is showing in
red
> which indicates something is quite right.
> Mike
>
> "Tom Ogilvy" wrote:
>
> > in the ThisWorkbook module
> >
> > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > ThisWorkbook.Saved = True
> > End Sub
> > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> > Boolean)
> > cancel = True
> > End Sub
> >
> > After you add the code, you will have to disable macros manually, then
save
> > the workbook.
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Mike" <[email protected]> wrote in message
> > news:[email protected]...
> > > I have inserted various code(s) in vbs "this workbook" using private
sub
> > > auto_close
> > > and private sub work_book close() after other code to close
excel/workbook
> > > without saving or displaying a prompt but none seems to work.
> > > I have used 'application.displayalerts = false'.' application.quit',
> > > 'thisworkbook.close savechanges:=false' etc etc in a variety of
different
> > > combinations.
> > > Perhaps I am placing the code in the wrong place or perhaps I should
be
> > > saving before quitting or ..... ?
> > > Can anyone help ?
> > > Mike
> >
> >
> >

Tom,
Many many thanks, your code does work.
However an unforeseen problem has arisen.
I have a 'save as' macro that has been disabled by your code.
What I am trying to achieve is for nobody to alter my master workbook so
when it is closed it returns to it's pristine state, but also allowing the
user to save the work he has done as a different file name.
Any ideas ?
Thanks for all your trouble.
Mike

"Tom Ogilvy" wrote:

> Since I copied it out of a workbook, it is right. Perhaps you didn't
> correct for wordwrap.
>
> here is one adjusted so you shouldn't have a wordwrap problem
>
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> ThisWorkbook.Saved = True
> End Sub
>
> Private Sub Workbook_BeforeSave( _
> ByVal SaveAsUI As Boolean, _
> Cancel As Boolean)
> Cancel = True
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Mike" <[email protected]> wrote in message
> news:[email protected]...
> > Tom,
> > Me again.
> > Have copied/pasted your code in but the second private sub is showing in
> red
> > which indicates something is quite right.
> > Mike
> >
> > "Tom Ogilvy" wrote:
> >
> > > in the ThisWorkbook module
> > >
> > > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > > ThisWorkbook.Saved = True
> > > End Sub
> > > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> > > Boolean)
> > > cancel = True
> > > End Sub
> > >
> > > After you add the code, you will have to disable macros manually, then
> save
> > > the workbook.
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "Mike" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I have inserted various code(s) in vbs "this workbook" using private
> sub
> > > > auto_close
> > > > and private sub work_book close() after other code to close
> excel/workbook
> > > > without saving or displaying a prompt but none seems to work.
> > > > I have used 'application.displayalerts = false'.' application.quit',
> > > > 'thisworkbook.close savechanges:=false' etc etc in a variety of
> different
> > > > combinations.
> > > > Perhaps I am placing the code in the wrong place or perhaps I should
> be
> > > > saving before quitting or ..... ?
> > > > Can anyone help ?
> > > > Mike
> > >
> > >
> > >
>
>
>

Mike

Save your master workbook as a Template(*.xlt) and have the user create a new
workbook from that.


Gord Dibben Excel MVP

On Wed, 22 Jun 2005 14:47:03 -0700, "Mike" <[email protected]>
wrote:

>Tom,
>Many many thanks, your code does work.
>However an unforeseen problem has arisen.
>I have a 'save as' macro that has been disabled by your code.
>What I am trying to achieve is for nobody to alter my master workbook so
>when it is closed it returns to it's pristine state, but also allowing the
>user to save the work he has done as a different file name.
>Any ideas ?
>Thanks for all your trouble.
>Mike
>
>"Tom Ogilvy" wrote:
>
>> Since I copied it out of a workbook, it is right. Perhaps you didn't
>> correct for wordwrap.
>>
>> here is one adjusted so you shouldn't have a wordwrap problem
>>
>> Private Sub Workbook_BeforeClose(Cancel As Boolean)
>> ThisWorkbook.Saved = True
>> End Sub
>>
>> Private Sub Workbook_BeforeSave( _
>> ByVal SaveAsUI As Boolean, _
>> Cancel As Boolean)
>> Cancel = True
>> End Sub
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>>
>>
>> "Mike" <[email protected]> wrote in message
>> news:[email protected]...
>> > Tom,
>> > Me again.
>> > Have copied/pasted your code in but the second private sub is showing in
>> red
>> > which indicates something is quite right.
>> > Mike
>> >
>> > "Tom Ogilvy" wrote:
>> >
>> > > in the ThisWorkbook module
>> > >
>> > > Private Sub Workbook_BeforeClose(Cancel As Boolean)
>> > > ThisWorkbook.Saved = True
>> > > End Sub
>> > > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
>> > > Boolean)
>> > > cancel = True
>> > > End Sub
>> > >
>> > > After you add the code, you will have to disable macros manually, then
>> save
>> > > the workbook.
>> > > --
>> > > Regards,
>> > > Tom Ogilvy
>> > >
>> > >
>> > > "Mike" <[email protected]> wrote in message
>> > > news:[email protected]...
>> > > > I have inserted various code(s) in vbs "this workbook" using private
>> sub
>> > > > auto_close
>> > > > and private sub work_book close() after other code to close
>> excel/workbook
>> > > > without saving or displaying a prompt but none seems to work.
>> > > > I have used 'application.displayalerts = false'.' application.quit',
>> > > > 'thisworkbook.close savechanges:=false' etc etc in a variety of
>> different
>> > > > combinations.
>> > > > Perhaps I am placing the code in the wrong place or perhaps I should
>> be
>> > > > saving before quitting or ..... ?
>> > > > Can anyone help ?
>> > > > Mike
>> > >
>> > >
>> > >
>>
>>
>>

Gord,
This is doing my head in.
To be able to save master as a template I must first deactivate Toms' code
because it isn't allowing me to 'save as'. If I reactivate after that then
nobody will be able to 'save as'. If I don't reactivate then I lose the exit
function (Toms Code) that I was originally trying to achieve.
In a copy master I have the following code which works well when the
activeworkbook
quit button (lower 'X') is used but I can't figure out a way of achieving
the same result
when I use the application quit button (upper 'X'). When I use this latter
button the
workbook closes but leaves the tool bar at the top but if I have a second
workbook open the first book closes but the second one just freezes.
If any of this makes sense and you can help I would most grateful.
Mike

Private Sub Workbook_beforeclose(cancel As Boolean)
ThisWorkbook.Saved = True
ThisWorkbook.Close
ActiveWorkbook.Close
Application.Quit
End Sub

"Gord Dibben" wrote:

> Mike
>
> Save your master workbook as a Template(*.xlt) and have the user create a new
> workbook from that.
>
>
> Gord Dibben Excel MVP
>
> On Wed, 22 Jun 2005 14:47:03 -0700, "Mike" <[email protected]>
> wrote:
>
> >Tom,
> >Many many thanks, your code does work.
> >However an unforeseen problem has arisen.
> >I have a 'save as' macro that has been disabled by your code.
> >What I am trying to achieve is for nobody to alter my master workbook so
> >when it is closed it returns to it's pristine state, but also allowing the
> >user to save the work he has done as a different file name.
> >Any ideas ?
> >Thanks for all your trouble.
> >Mike
> >
> >"Tom Ogilvy" wrote:
> >
> >> Since I copied it out of a workbook, it is right. Perhaps you didn't
> >> correct for wordwrap.
> >>
> >> here is one adjusted so you shouldn't have a wordwrap problem
> >>
> >> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> >> ThisWorkbook.Saved = True
> >> End Sub
> >>
> >> Private Sub Workbook_BeforeSave( _
> >> ByVal SaveAsUI As Boolean, _
> >> Cancel As Boolean)
> >> Cancel = True
> >> End Sub
> >>
> >> --
> >> Regards,
> >> Tom Ogilvy
> >>
> >>
> >>
> >> "Mike" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Tom,
> >> > Me again.
> >> > Have copied/pasted your code in but the second private sub is showing in
> >> red
> >> > which indicates something is quite right.
> >> > Mike
> >> >
> >> > "Tom Ogilvy" wrote:
> >> >
> >> > > in the ThisWorkbook module
> >> > >
> >> > > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> >> > > ThisWorkbook.Saved = True
> >> > > End Sub
> >> > > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> >> > > Boolean)
> >> > > cancel = True
> >> > > End Sub
> >> > >
> >> > > After you add the code, you will have to disable macros manually, then
> >> save
> >> > > the workbook.
> >> > > --
> >> > > Regards,
> >> > > Tom Ogilvy
> >> > >
> >> > >
> >> > > "Mike" <[email protected]> wrote in message
> >> > > news:[email protected]...
> >> > > > I have inserted various code(s) in vbs "this workbook" using private
> >> sub
> >> > > > auto_close
> >> > > > and private sub work_book close() after other code to close
> >> excel/workbook
> >> > > > without saving or displaying a prompt but none seems to work.
> >> > > > I have used 'application.displayalerts = false'.' application.quit',
> >> > > > 'thisworkbook.close savechanges:=false' etc etc in a variety of
> >> different
> >> > > > combinations.
> >> > > > Perhaps I am placing the code in the wrong place or perhaps I should
> >> be
> >> > > > saving before quitting or ..... ?
> >> > > > Can anyone help ?
> >> > > > Mike
> >> > >
> >> > >
> >> > >
> >>
> >>
> >>
>
>

Tom,
Thanks - everything now works fine.
I need however to be able to save one sheet so that the user can enter and
change their particular defaults. I tried pretty most things including
running a save macro but nothing seems to work.
Any oideas ?
Mike

"Tom Ogilvy" wrote:

> If the code prevents saving the workbook, then how are you going to save the
> workbook after you enter the code.
>
> What you have to do is go to the immediate window and enter
>
> Application.EnableEvents = False
>
> now save the workbook and close it.
>
> Go to the immediate window and enter
>
> Application.EnableEvents = True
>
> to reenable events.
>
> --
> Regards,
> Tom Ogilvy
>
> "Mike" <[email protected]> wrote in message
> news:[email protected]...
> > Tom,
> > Thanks for that. I haven't tried it yet 'coz I didn't understand the
> > 'disable macros first' comment and the workbook contains loads of macros
> > which would take for ever to disable. I am wanting other users not to be
> able
> > to save changes when using the excel 'X' 's (quit buttons) nor to see a
> save
> > prompt.
> > Mike
> >
> > "Tom Ogilvy" wrote:
> >
> > > in the ThisWorkbook module
> > >
> > > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > > ThisWorkbook.Saved = True
> > > End Sub
> > > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> > > Boolean)
> > > cancel = True
> > > End Sub
> > >
> > > After you add the code, you will have to disable macros manually, then
> save
> > > the workbook.
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "Mike" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I have inserted various code(s) in vbs "this workbook" using private
> sub
> > > > auto_close
> > > > and private sub work_book close() after other code to close
> excel/workbook
> > > > without saving or displaying a prompt but none seems to work.
> > > > I have used 'application.displayalerts = false'.' application.quit',
> > > > 'thisworkbook.close savechanges:=false' etc etc in a variety of
> different
> > > > combinations.
> > > > Perhaps I am placing the code in the wrong place or perhaps I should
> be
> > > > saving before quitting or ..... ?
> > > > Can anyone help ?
> > > > Mike
> > >
> > >
> > >
>
>
>