Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / May 2008

Tip: Looking for answers? Try searching our database.

How can just opening & closing a workbook prompt the Save? dialog?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
baobob@my-deja.com - 26 May 2008 19:53 GMT
I have an Excel 2002 autoexec workbook which starts automatically when
Excel launches, sets some defaults, then closes its own workbook
again.

It neither reads nor writes to the worksheet, or does anything else to
change the workbook in any way that would prompt the "Save your
changes?" dialog on exit. It just sets some Tools / Options defaults
and exits. It's run for years without incident.

But recently I tweaked it and somehow got it into a state in which the
Save dialog was appearing on close.

To debug it, I moved it out of \XLSTART so that I could load it
manually. I commented the code down so that ALL THE MACRO DID was
close its own workbook without doing anything else. The Save dialog
STILL appeared.

So, after a couple of days of pulling my hair out, I threw in the
towel and saved the workbook as it requested, and the dialog no longer
appears.

Anyone have a notion of what was happening?

Thanks.

***
baobob@my-deja.com - 26 May 2008 20:13 GMT
P.S. I spoke too soon. The dialog has come back, and it still appears
every time I run the macro, even running it manually, and even
executing nothing except simply closing again.

Thanks.

***
baobob@my-deja.com - 26 May 2008 20:25 GMT
P.P.S. I think I spoke even too sooner.

I don't think it has to do with the macro at all.

The macro is Sub Auto_Open, so it executes automatically on load.

I changed its name and don't run the macro at all any more.

On loading the workbook then File / Close, I get the Save dialog. So
is there something in the sheet itself that somehow dynamically
changes upon loading?

Answer: There may be. There is a formula in cell A1. I gotta debug it.

Guess y'all can ignore this thread 'til I find out how the hell I'm
shooting myself in the foot.

***
baobob@my-deja.com - 26 May 2008 20:38 GMT
Aaagggghh!

My fault. I commented out my formula and that stopped the Save dialog.

So it's programmer recreational drug use--again.

***

But do I have a suspicion that Excel 2002 flags the Save condition
differently than previous versions? Because I think I've used that
formula for some years without incident.

***
Per Jessen - 26 May 2008 20:40 GMT
On 26 Maj, 21:25, bao...@my-deja.com wrote:
> P.P.S. I think I spoke even too sooner.
>
[quoted text clipped - 14 lines]
>
> ***

Hi

If the value in the formula in A1 changes, then excel will always
prompt to save the workbook.

To avoid this, you can use a workbook_beforeClose event like below:

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

Regards,
Per
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.