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!


0
Robert
11/21/2009 10:17:46 PM
excel.programming 6508 articles. 2 followers. Follow

4 Replies
640 Views

Similar Articles

[PageSpeed] 14

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/showthread.php?t=156201

[url="http://www.thecodecage.com"]Microsoft Office Help[/url]

0
Simon
11/22/2009 2:52:28 AM
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" <Simon.Lloyd.4217rd@thecodecage.com> wrote in message 
news:Simon.Lloyd.4217rd@thecodecage.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
> --------------------
>
>
> 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/showthread.php?t=156201
>
> [url=&quot;http://www.thecodecage.com&quot;]Microsoft Office Help[/url]
>
> 


0
Charles
11/22/2009 8:45:10 AM
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" <Charles@DecisionModels.com> wrote in message 
news:4b08fa15$0$2480$db0fefd9@news.zen.co.uk...
> 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.
>

0
Robert
11/22/2009 10:33:45 AM
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" <nobody@gmail.com> wrote in message 
news:ds8Om.70998$Wf2.53029@newsfe23.iad...
> 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" <Charles@DecisionModels.com> wrote in message 
> news:4b08fa15$0$2480$db0fefd9@news.zen.co.uk...
>> 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.
>>
>
> 


0
Charles
11/22/2009 4:12:01 PM
Reply:

Similar Artilces:

ERROR 80040E37 while trying to save changes offline
Error 80040E37 comes out when I am trying to save changes while being offline. Prehistory: I have moved CRM server on the same domain to another server and different OU. All the export, import, mapping procedures went smoothly. And now everything works just fine while being online, no errors during SQL replication. I have tried to remove replication registry entries from client machine, delete replication databases from client machine - no use. Nerijus ...

Access 97 won't save changes to form design view.
I make changes in design view but they will not save. If I close the program, it asks me if I want to save but clicking yes does nothing. The program will not close until I click no. This is a data base made in Access 95 that has been converted to 97 file extension. The database works fine as far as entering and finding data. Of course, the help files are of no use... Thanks in advance for any help. Bill If the database .mdb file is out on a network, make a copy of it and put it on your PC. Can you make and save changes now? -- Jerry Whittle, Microsoft Access MVP Light. S...

save changes to calendar
I have Publisher 2003 and would like to make a calendar. How to save changes to the design, and then be able to apply modified design across any selected date range? I do not want to go in and modify each and every page. Thanks for your help. The only workaround is to modify your first month, copy/paste, edit the numbers and the month name. Insert 11 pages, one for each month. Calendars are tables, changing the numbers is easy. Getting publications to look the way we desire always takes a little work. -- Mary Sauer http://msauer.mvps.org/ "Amygdala" <Amygdala@discuss...

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! Right click the worksheet tab that your concerned with and paste this in: Code: -------------------- Priv...

How to prevent "Save changes to Untitle?"
Hi guys, My app is SDI, View-Doc architecture developed in VS6.0, win2k. When program exits without Save, I like to prevent program to ask "Save changes to Untitle?" in some cases. Any ideas please? Thanks Bob Not sure this is a good way but it seems works. Thanks. void CMainFrame::OnClose() { // ... GetActiveDocument()->SetModifiedFlag(FALSE); CFrameWnd::OnClose(); } "Bob Lee" <bob.lee@vmsl.co.nz> wrote in message news:elrg13-6g1.ln1@beaker.vmsl.co.nz... > Hi guys, > My app is SDI, View-Doc architecture developed in VS6.0, win2k. > When program...

Using PageSetup Default does not save changes when next opened
In Word (MS Office Word 2003 SP3) I make font (from TimesNewRoman to Arial) & font size (12 to 10), I go to PageSetup hit the Default button. My next opening of Word I again have TimesNewRoman 12 pt. I have made changes to NORMAL template many times over years, never had this issue. Presumably when you click Default... you also answer yes to the ensuing dialog? There are some add-ins that prevent Normal.dot from being saved; there are ways to work around them, but a direct solution is just to force an immediate save of Normal. Press the Shift key while clicking on the File...

How to unsort my excel columns after save changes?
If I save changes to the excel file after sorting, I can't able to unsort it as original. Is there any option to do so in excel? You cannot undo the sort after saving. The best to do before sorting, is to add an ID column with numbers running from 1. Then if you want to restore your sort, you can then sort on this column. "RAFFI" wrote: > If I save changes to the excel file after sorting, I can't able to unsort it > as original. Is there any option to do so in excel? Hi As sorting in Excel means overwriting all data in new order - definitely no! The only opti...

"Do you wish to save CHANGES" question
In both Excel and Word, when I close a document, I am asked "do you wish to save changes?" when I have not made any changes? This question was not asked in Office 2000 and I find it distracting to say the least. Also, I don't want to change the date of the document was modified, if indeed it was not modified. Gabby do the documents have any Links in them ? Or fields that could cause the document to automatically change ? For example, =NOW or =TODAY(), in Excel would have this effect. Regards Trevor "Gabby" <anonymous@discussions.microsoft.com> wro...

Cant save changes in subject line
I can not save changes in the subject line. I use DK MUI, have any of you ever seen the same problem? ...

Posting setup
Our client has noticed some flaws in the Posting Setup window. When you choose the series (eg. Purchasing) and Origin All, it always defaults to certain fields. For example, the Posting Date From is always defaulted to Batch. If you have selected Transactions for all the purchasing windows, and then you exit this window by clicking on OK, it actually overwrites all the windows so that they are all using Posting Date From Batch instead of Transaction. This raises concerns with our client and they believe that it needs to be improved. This has caused problems several times and they ha...

Prompt for to save changes
I am running Excel 2002 XP SP2 in Windows XP When I go to close a workbook called 'money' a message box pops up and ask if I want to save the changes in money.xls - three options = yes - no - cancel I have about 10 other workbooks and none of them prompt for this changes save. I am having problems with this workbook - posted under 'File In Use' at 3:11 PM today - and am wondering if there is a relationship here. Is there a way to stop this 'save changes' message box? I do not remember any settings for this message box. TIA Bill Chatfield And you're confiden...

"Do you want to save changes?" question
Is there a flag or variable or property that I can check that indicates whether or not the "Do you want to save changes?" dialog will appear when a workbook is closed?? If someone changes the contents of a cell and tries to close the workbook without saving, Excel knows that the user did not save the workbook. So, just to be more clear, how can I find out if Excel knows that a "save changes" is required?? thank you More than one option. Application.DisplayAlerts = False ActiveWorkbook.Close Application.DisplayAlerts = True Or: ActiveWorkbook.Clos...

Prompt user to save changes in a form
Hello... I figured out how to prompt my users to save their changes to an existing record in a form before closing. However, I was wondering how I can make it so it only prompts them on existing records and not new. Another words if they create a new record and add in their data I do not want the prompt to come up when they go to close the window. I hope I am explaining this correctly. =) Thank you in advance for any help you can give. Also...fyi...I am not very familiar with sql so I need the "dummy" version please =) Sorry again, I dont know a whole about all this. Were in...

make a duplicate table in access and save changes
I have a table that gives the rates of Dearness Allowance.These rates are given for different periods in percentages. ex: 01-01-2009 to 30-06-2009 37% 01-07-2009 to 30-11-2009 45% etc.. This is the default table and is applicable to almost 95% to 97% of employees. I want to make a duplicate of this table and display it in an unbound subform ( which is a continuous form). If the user makes any changes in it, it is then clear that Default rates are not applicable to him. Then, i want to store the data in a new table tblDeviations where i will store the EmployeeId also ...

Can I save changes to excel on a floppy?
I have a monthly expense I set up in excel. I have to go from office to office to different computers so if I add in an expense can or will the update be saved in the floppy or how can I save the changes? You never open a file on a floppy nor should you ever save a file back to a floppy. Insert the floppy, save the file to the hard disk, open the file (on the hard disk), make your changes, save the file (on the hard disk), close the file, copy the file back to the floppy, move on to the next office. Alternate solution - get a network and stay in your own office. ;-) -- JoAnn Paules M...

Save changes for a custom configuration section
Hi there, I've defined a 'Foo' configuration section in my App.config file: <?xml version="1.0" encoding="utf-8" ?> <configuration> <configSections> <section name="Foo" type="FooLib.FooConfigSection, FooLib"></section> </configSections> <Foo label="Foo Label"></Foo> </configuration> ....and defined a custom ConfigSection class: public class FooConfigSection : ConfigurationSection { [ConfigurationProperty("label", IsRequired=tru...

Save changes?
Every time i open an Excel-file saved in an earlier version I get the same question when closing the file: Microsoft Office Excel recalculates the formulas when files, saved in earlier versions of Excel are opened. Do you want to save..... Is there any way to close this function so that the dialog box does not appear every time? Not really, but why do you respond to it with a No everytime ? Save (or select Yes) once, and it is not saved in an earlier version anymore. HTH. Best wishes Harald "Lena_excel" <Lena_excel@discussions.microsoft.com> skrev i melding news:0DC74CAB...

Changed background/font but won't save changes
I'm trying to change an existing presentation created in old version of PP (2003 I believe). Currently working in Office 2007 and trying to get background and font color to be straight black and white format. Found the quick fix in View although that change won't save when I exit and return to the presentation. What am I missing? Hi Tammi, The view options just control the current appearance of the information, not the information itself. You will need to change each shape, font and image individually to pure black or pure white. There is no automatic way to cha...

"Save changes" question.
Why do I get a message asking me if I want to save the changes made to the spreadsheet when I didn't do a thing to it? I merely opened it up, looked at data in a cell, and closed it. Nothing was changed, added, deleted, or even highlighted. Thanks, Hi Paul Maybe you have Volatile function See the following webpage http://www.decisionmodels.com/calcsecretsi.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Paul" <anonymous@discussions.microsoft.com> wrote in message news:2218b01c45d53$921b06d0$a601280a@phx.gbl... > Why do I get a message asking me if I ...

Save Changes question?
I have a form with 2 subforms. Our construction managers will be able to edit or add new projects. I have allowed for edits and additions in properties. Will that information automatically be saved? If not, what do I do to make that happen? Thanks so much!!! "Golfinray" <Golfinray@discussions.microsoft.com> wrote in message news:65D974EA-C044-4118-8AB4-E1031DEA272A@microsoft.com... >I have a form with 2 subforms. Our construction managers will be able to >edit > or add new projects. I have allowed for edits and additions in properties. > Will that information ...

Unable to save changes to labels
Hi! I'm experiencing a weird problem. When I change the label of an attribute on the form and click save or save and close, CRM doesn't save the changes. It just keeps hanging on to the attribute name instead of using the text from the label I just changed. Please help! This is pretty urgent... Reinstalling update rollup 2 v2 (re-released) solved the problem. I think the first update rollup 2 created this problem. Pk "Pk" wrote: > Hi! > > I'm experiencing a weird problem. When I change the label of an attribute on > the form and click save or save a...

unable to save changes to shared spreadsheet (07)
I have a large spreadsheet that resides on our Windows 2003 server and is accessed via network shares at our location and terminal server at one remote location. It is currently saved in office 2003 format (XLS) and I had to install Excel 2007 since Microsoft released an update a couple of weeks ago that disabled my sharing Excel 2003 spreadsheets. It by default takes the latest version as the on that it will open with. Intermittently, my users remote users receive , "unable to save file, locked by Administrator" this is me and I am not using. They must do a save as and th...

Excel asks to save changes when noen are made
I have several spreadsheets that make Excel (2003) ask whether to save the changes before closing the file, even if no changes are made (e.g. open the file and close it - or select exit - without doing anything else). I have pinned down one cause of this; it seems that the OFFSET function can make Excel behave this way (e.g. an otherwize blank spreadsheet with the function =offset(a1,12,0) ). - Is there a way to avoid this question (unless I have made changes)? - Why does Excel think that changes have been made? dOinK Hi you probably have some volatile functions in your spreadsheet (e.g....

"do you want to save changes" when I close outlook
I have recently upgraded to office 2007, including outlook 2007. Now when I add senders to the blocked sender list and then close outlook I am prompted “do you want to save changes”. If I click “yes” I then get a message window “the item cannot be saved because it was changed by another user or in another window”. Also asks “Do you want to make a copy in the default folder for the item”. This only happens when senders are added to blocked sender list. I would like to eliminate the prompt as the senders still seem to be added to the list. Previous verion of outlook was 2003. Usin...

Selectively disable "Save changes to Untitled?" dialog
I wish to disable the dialog box that appears when a modified document is being closed. I think it is simple to do in case of a simple SDI or MDI application. However, my application is an MDI application similar to the CHKBOOK sample application in MFC (MDI child windows showing different views of the same document). The problem - I get "Save changes to the Untitled?" dialog box for each MDI child window when it's closed. Ideally, I would like to get this dialog box only when the last active window for the document is closed. Is there any way to accomplish this? Thanks for you...