Toggling save warning message on or off

  • Thread starter Thread starter c1802362
  • Start date Start date
C

c1802362

This is more of an Excel question than a VBA question, but....

I recently wrote a large VBA program that retrieves excel files (.xlsx) from a network directory, updates each file with downloaded system data, reduces the data, then writes the updated excel file back to its source location..

The source code is located in a separate macro-enabled file (.xlsm)

I've written numerous programs like this in the past with no issues. In this instance, opening up any of the modified files to view the updates results in a 'Do you want to save the changes...' pop-up upon exiting the file, even if there have been no changes.

Where in the system is this type of message being triggered from? I'm not finding any properties that flag this type of action.

The system is running Windows 7/MS Office 2007

Art
 
Some food for thought...

There's a number of reasons why the alert is displayed when no user
changes have occured. The most common is when cells contain formulas
and CalculationMode is set to Automatic. Excel calcs these on opening
and so considers the file has changed and needs to be saved. Editing
cells is not the only action that can trigger this alert. For example,
if a user expands/collapses an outline a change occured. Actions that
fire certain events can trigger the alert as well.

You could...

...have your XLSM code turn Application.DisplayAlerts off and set
Application.Calculation to xlCalculationManual *before* it opens files.

...add an events handler class to your XLSM that handles all Excel
events and workbook/worksheet events so as to control the behavior of
your project under specific runtime conditions. This is fairly complex
but once the class is set up it's reusable in other projects. This
would be my preference because I can trap any workbook's BeforeClose
event and run my own code regarding a file's 'Saved' state (or any
other condition I choose).

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
c1802362 said:
This is more of an Excel question than a VBA question, but....

I recently wrote a large VBA program that retrieves excel files (.xlsx) from a network directory, updates each file with downloaded system data, reduces the data, then writes the updated excel file back to its source location.

The source code is located in a separate macro-enabled file (.xlsm)

I've written numerous programs like this in the past with no issues. In this instance, opening up any of the modified files to view the updates results in a 'Do you want to save the changes...' pop-up upon exiting the file, even if there have been no changes.

Where in the system is this type of message being triggered from? I'm not finding any properties that flag this type of action.

The system is running Windows 7/MS Office 2007

Art
Click to expand...



open new excel file and write formula
=now ()

next, save it, close , reopen and try to close.
Do you see a message?

That is why Excel asks you if you want to save changes. Values in your
file have been modified. It does not have to be done by user. Formula
can modified its value without user interaction.
 
witek said:
open new excel file and write formula
=now ()

next, save it, close , reopen and try to close.
Do you see a message?

That is why Excel asks you if you want to save changes. Values in your
file have been modified. It does not have to be done by user. Formula
can modified its value without user interaction.
Click to expand...


the other reason can be if your program saved xlsx files not fully
recalculated. It recalculates when somebody opens it next time.
Check in your xlsm program if calculation mode is set to automatic or
recalculate file manually before closing.
 
witek said:
the other reason can be if your program saved xlsx files not fully
recalculated. It recalculates when somebody opens it next time.
Check in your xlsm program if calculation mode is set to automatic or
recalculate file manually before closing.
Click to expand...


and the last idea.

Does it ask you to save changes every time you open it (as interactive
user) or only first time?

If it asks only once (during first opening after modification) check
your xlsm program for calculation mode

if it asks every time you open xlsx. Search xlsx file for volatile formulas.
 
witek wrote: >
Click to expand...

The target files, which always ask if you want to save (even if the file is opened then immediately closed) are static - there are no calculated ranges in the files,and no VBA code, but there are a number of conditionally formatted cells.

The source code file already has an automatic recalc of the system (part of my standard utilites that reset the environment before exiting), and I've never had this issue before

I erased all conditional formatting, saved the file, and it is still happening. I tried forcing a recalc, saved it, and nothing changed.

Art
 
c1802362 said:
The target files, which always ask if you want to save (even if the file is opened then immediately closed) are static - there are no calculated ranges in the files,and no VBA code, but there are a number of conditionally formatted cells.

The source code file already has an automatic recalc of the system (part of my standard utilites that reset the environment before exiting), and I've never had this issue before

I erased all conditional formatting, saved the file, and it is still happening. I tried forcing a recalc, saved it, and nothing changed.

Art
Click to expand...


Does that also happen on another computer?
Does that happen with new empty file?
Try to delete sheet by sheet and see when it stops.
 
yes
yes

found it!

I had given the blank data sheet to a colleague to format it in the required style of the report. Apparently he buried '=TODAY()' in a cell way off the working area. As you previously stated, this was forcing a recalc eveery time the workbook was opened...

Ugggghhh!

Art
 
c1802362 said:
yes
yes

found it!

I had given the blank data sheet to a colleague to format it in the required style of the report. Apparently he buried '=TODAY()' in a cell way off the working area. As you previously stated, this was forcing a recalc eveery time the workbook was opened...

Ugggghhh!

Art
Click to expand...


:)
 
Back
Top