Preventing "save changes" dialog box???

  • Thread starter Thread starter Robert Crandal
  • Start date Start date
R

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!
 
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

Simon Lloyd said:
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!
Click to expand...


--
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
Click to expand...
 
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)
 
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
 
Back
Top