Finding what has changed.

[DocBrown]

Is there a way to determine what has changed in a workbook?

I have a template file I created. It has two worksheets and extensive
macros. When I open the file as a template and then close it, I get the
prompt 'Do you want to Save'. I'd like to find out what Excel thinks has
changed in the file so I can either change the code to not make that change
or save the file in the code so the prompt doesn't occur.

As a test, I disabled the Auto_open macro thinking something in the coding
was triggering the saved to be false. But the prompt persists. I'm continuing
to debug this but any insight would help greatly.

Thanks,
John

 

[Jacob Skaria]

Is that template saved in previous version. Microsoft Office Excel
recalculates the formulas when files, saved in earlier versions of Excel are
opened; or do you have any volatile formulas; or any code within the Sheet
activate/selection change/change events..If you are looking to disable the
prompt use

Application.DisplayAlerts = False
ActiveWindow.Close
Application.DisplayAlerts = True

OR

ActiveWindow.Close SaveChanges:=False 'do not save changes

OR

ActiveWindow.Close SaveChanges:=True 'save changes made

If this post helps click Yes

 

[DocBrown]

Thanks for the quick reply.

I'm currently using Excel 2002 SP3 (Office XP). It happens even when I
reopen the WB in the same version it was created and opened.

No volatile functions that I know of. I assume I'd have to specify that if I
were using them?

I put debug.print in Worksheet_change and Workbook_SheetChange and they
didn't get called. I need to check the Activate event.

I'm trying to make it so that I only save the file if it is really needed,
i.e., my macros may make a change that needs to be saved, or the user makes a
change.

I have a pet peeve that I'm trying to avoid. I use the file creation date to
track when the file was really modified by the user. If I indiscriminetly
save the file, the date is always changed and I may not want that.

 

[Jacob Skaria]

--In the general module you can delcare a boolean variable
Public blnChanged As Boolean

'and use the 'This Workbook' event SheetChange to track any changes made..
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
blnChanged = True
End Sub

'and in your code for closing the window you can use that variable as below
ActiveWindow.Close SaveChanges:=blnChanged


--The below link around how to suppress "Save Changes" prompt when you close
a workbook in Excel will be useful for future references...

http://support.microsoft.com/kb/213428

If this post helps click Yes

 

[Joel]

Changing the active cell, switching worksheets, or window view will also
cause excel to display the message. there may not be any data that has
changed that will trigger the message.