Preventing "save changes" dialog box???

[Robert Crandal]

My spreadsheet data is stored in the range A1:J20. Therefore, I do not
care if a user plays with or modifies cells that are outside of this range.

Can I somehow program Excel to prevent the "Do you want to save
changes?" dialog box if a user changes cells that are out of range and
then s/he closes the workbook??

I basically only want the "save changes" dialog box to be prompted
when cells within the A1:J20 range are modified.

Thank you!

 

[Charles Williams]

If the user changes something in A1:J20 and then changes something outside
a1:j20 .Saved will be True but shouldnt be.

One more complicated way of handling this would be to maintain a hidden
shadow copy of A1:J20 somewhere which gets updated at each Save.
Then you could check in the Worksheet_Change event to see if the shadow copy
matched the real copy, and only set .saved to True if they matched.

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

Right click the worksheet tab that your concerned with and paste this
in:


Code:
--------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(ActiveCell, Range("A1:J20")) Is Nothing Then
ThisWorkbook.Saved = True
End If
End Sub
--------------------

My spreadsheet data is stored in the range A1:J20. Therefore, I do not
care if a user plays with or modifies cells that are outside of this
range.

Can I somehow program Excel to prevent the "Do you want to save
changes?" dialog box if a user changes cells that are out of range and
then s/he closes the workbook??

I basically only want the "save changes" dialog box to be prompted
when cells within the A1:J20 range are modified.

Thank you!

--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile:
http://www.thecodecage.com/forumz/member.php?userid=1
View this thread:
http://www.thecodecage.com/forumz/showthread.php?t=156201

Microsoft Office Help

 

[Robert Crandal]

Good catch on that flaw in the code...

As an alternative to the idea that you mention below, how about if
I create a global variable named something like "gWasModified"
and set it to false. If anything in A1:J20 is modified at any time,
then I could set "gWasModified" to true. In the worksheet deactivate
routine, couldn't I just check the global variable and then
set "ThisWorkbook.Saved" to true or false based on my global variable???

(I hope that makes sense, haha)

 

[Charles Williams]

Yup, sounds like that would work better than the shadow copy method:

Set global to false at workbook open

in Worksheet change
- set global to true if A1:J20 modified
- if global is false (a1:J20 has not been modified since last Save) then set
..Saved to true

At workbook save set global to false

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com