Something so simple, yet so tricky... (last request for help :-)

[Marvin Hlavac]

I've asked this already and I've tried various suggested solutions.

What I need seems simple: when I close my workbook I don't want to save
changes and I don't want the "Save before quitting?" to appear. The various
ways I've tried fail in one instance: when user leaves the workbook open and
attempts to shutdown or restart the computer s/he still gets the "Save
before quitting?".

This is my last request for help before I give up

Thanks in advance,

 

[Ken Wright]

Perhaps:-

ActiveWorkbook.Close SaveChanges:=False

 

[Ken Wright]

In the Before_Close option of the ThisWorkbook module that is



ThisWorkbook Module:-

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveWorkbook.Close SaveChanges:=False
End Sub

 

[Marvin Hlavac]

Hi Ken,

Thanks for your reply. Still the same result, though. It works well in most
cases but there is one instance where it fails to work as intended: Try to
make some change to the workbook and leave the workbook open and attempt to
restart your computer. It will ask you "Save before quitting?". In my
workplace they have a habit of not closing any applications and restarting
their computers when they think something is not working right. I just want
to avoid them accidentally saving changes to this one workbook this way.

 

[Ken Wright]

Guess I'm struggling there - My initial response would be tell them that is
'Tough and they should have more sense', but you probably can't do that ;-)

You can please Most of the users Most of the time, but you can never please All
of the users All of the time

 

[Ken Wright]

OK, slight tweak on one of Chip's. If they behave like fools, treat them like
fools

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Me.Saved = False Then
If MsgBox("STOP!!! - Do you want to Save the book? (But be VERY sure " _
& "you want to, else you are TOAST!!!", vbYesNo) = vbYes Then
Application.EnableEvents = False
Me.Save
Application.EnableEvents = True
End If
Me.Saved = True
End If
End Sub

 

[Marvin Hlavac]

I like your sense of humour Ken. It looks like I may need to resort to that

 

[Ken Wright]

LOL - getting late now, so everything's a bit tounge-in-cheek after a certain
time

 

[Debra Dalgleish]

You could save the workbook as a template, and users would open a copy
of the file. That way, the original file won't be affected by any
changes the users make.

 

[JE McGimpsey]

OK, slight tweak on one of Chip's. If they behave like fools, treat
them like fools

I suppose it would be churlish to point out that this solution doesn't
work if the user sets security to High (or disables macros) so that the
event macro never runs...

....the fool being then blissfully free to save the workbook when
rebooting.

 

[Marvin Hlavac]

I tried it and interestingly, in the event of user attempting shutdown or
restart of the pc while the workbook is open Excel will give it's own "Save
before quitting?" and the humorous one will not show :-(

 

[Ken Wright]

OK, that's it - Now I WANT that user to be toast - I WANT him to lose it ALL - I
HOPE he blows it all away - It serves him right!!!!!!!!! <grin>

Think I'm going to bed shortly

 

[Marvin Hlavac]

All genius solutions are very simple. Why didn't I think of that? Thanks
Debra!!!

--
Regards,
Marvin Hlavac
Toronto, Canada

P.S. This solution has just one last little thing... I just noticed when you
save a file as a template Excel will in the Title bar display the file name
fallowed by number "1". I wish there was a way to eliminate the numbering.
(Is there?).

 

[Debra Dalgleish]

Your question was buried in your signature line, and I just noticed it:

'=================
P.S. This solution has just one last little thing... I just noticed when
you save a file as a template Excel will in the Title bar display the
file name fallowed by number "1". I wish there was a way to eliminate
the numbering. (Is there?).
'=====================

I don't know of any way to change this behaviour.

 

[Marvin Hlavac]

I don't know of any way to change this behaviour.

That's no problem Debra. Thanks. Now however another problem appeared: It
works perfectly on my WinXP / XL-XP machine but when I put the template file
on our work pc WinME / XL97 it took about a half a minute to open and from
that moment even the same file in XLS format which worked well before
started to take the same long time to open. Other XL files open normally on
that machine. Any ideas?

 

[Debra Dalgleish]

Perhaps the information in this post by Dave Peterson will help:

http://groups.google.com/groups?&[email protected]