Preventing "save changes" dialog box???
 

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!

Preventing "save changes" dialog box???
 

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


Robert Crandal;565884 Wrote:
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/sh...d.php?t=156201

Microsoft Office Help

Preventing "save changes" dialog box???
 

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" wrote in message
...

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


Robert Crandal;565884 Wrote:
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/sh...d.php?t=156201

Microsoft Office Help

Preventing "save changes" dialog box???
 

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" wrote in message
...
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.

Preventing "save changes" dialog box???
 

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

"Robert Crandal" wrote in message
...
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" wrote in message
...
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.