Preventing Save Pop Up

[kellie24]

How do I stop a pop up that occurs everytime I open and close an excel doc. If I try to right click and print, the "do you want to save changes" pop up appears even though no changes have been made. If I open and then immediately close the doc (w/o making changes), the same pop up appears as well. Any help would be appreciated

 

[kskinne]

hi kellie, welcome to the board

it sounds like you may have what are termed 'volatile' functions in your workbook - these are functions that have a volatile result that changes each time the workbook calculates. an example would be the =NOW() function, which returns the current date and time. since the time is constantly changing, so is the result of this formula. here are some other volatile functions:

RAND()
TODAY()
OFFSET()
CELL()
INDIRECT()
INFO()


even though you are not making any changes, a change in a formula like this will trigger excel to prompt you to save the change. see the excel help file or search this board for more information on, as well as a more complete list of, volatile functions.

post back if this doesn't answer your questions, or with any more details you can give about your workbook. if this is the source of your save as message, there are ways to avoid it


hth
kevin

 

[kellie24]

Is there a way to disable the pop up?

 

[martinee]

This doesn't disable it persay, but is a slight trick. In the beforclose event put in:

Me.Saved = True

It doesn't save it, but it makes excel think the file has been saved.

HTH

 

[kellie24]

I'm not that experienced in excel. I'm not sure what the beforeclose event is. If there is an easy way to explain it to me, please let me know. If not, don't worry about - thanks for your help

 

[kskinne]

kellie, the event martinee referred to needs to be added to your workbook - follow these steps:

in the left hand corner of your workbook, immediately to the left of the 'file' menu up on the menu bar, right-click on the excel icon. this should bring up a popup menu...select 'view code'. this will open the visual basic explorer and open the code pane for the workbook module. the code pane is the blank area on the right hand side of the new window. copy and paste the following code here:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Me.Saved = True
End Sub

make sure you save your work in this workbook before closing then, because as martinee stated this code makes excel think the workbook is in a saved state, and will not prompt you to save these or any other changes you make upon closing...

hth
kevin