how to stop a sheet from updating

[bigdan]

I don't think my subject line will explain this very well...

Here at work when I open certain Excel files, even if I don't modify anything, when closing it asks if I want to save changes. I'm not 100% sure why this is, but I'd guess there's some formula in there such as TODAY() which automatically modifies something in there.

1/ is my theory probably correct?
2/ how do i find this formula? haven't been able to do so, so far.

Thanks
Dan

 

[Zakkaroo]

I think you're correct that it's going to be some code running when you open the spreadsheet that causes this.

With the workbook open, press ALT + F11

This should bring up the code window.

The most likely place for this code to appear is in 'This Workbook', just double click on it to open the code.

But also have a look to see if there are any modules with code in, or any specific sheets that have code in.

 

[bigdan]

i assume when you're saying code and w/ the F11 advice, you're referring to a macro?

i thought of it more as some kinda formula hidden in some cell somewhere, either invisible or just not within the normal realms (eg. maybe cell# XZ179).

i did the alt-f11 thing but dont really know what im looking at. ive taken a screenshot but not sure how to put it up here. i'd rather not goto tinypic or something to upload the pic, am at work and dont want to get dinged for going to non work sites!

any way to upload a pic directly here?

 

[Zakkaroo]

I'll borrow a screenshot from The Google! To try and help.

http://www.bqe.com/archioffice2010/v8Manual/Advanced_Topics/Excel_VBA_Editor_Code.jpg

The bit you are looking for is at the top left.
It will show VBAProject(Name of your sheet.xls)

Underneath you will have a folder called Microsoft Excel Objects
If you expand this folder (as shown in the screenshot above), you should see the names of all of your sheets and also something called ThisWorkbook

If you double click on this workbook, you MIGHT then see some code in the large pane on the right hand side. In theory it would start with:

Private Sub Workbook_Open()

This is code that runs when you open the workbook. If there is something in here, it is proably what causes the document to want to save whenever you try and close it.

I don't know if a CELL formula could be causing the issue. If it is, then I don't know anyway to find it apart from manually checking each and every cell value.

 

[bigdan]

for some reason im not seeing the window on the right side. whats it called / how do i bring it up?

thanks for the help!

 

[Zakkaroo]

for some reason im not seeing the window on the right side. whats it called / how do i bring it up?

thanks for the help!

The window on the right is where all the code goes (the bit in the screenshot that says Sub workbook_open())

To access this you need to double click on something in the window on the left.

So, for example, in the screenshot, I would Double Click on ThisWorkbook and it would load the code on the right. The right hand side MAY be completely blank / white. If it is, it just means that no-one has put any code in there.

So, for each sheet, i would double click to see if there's any code. I would also have a look to see if there were any MODULES on the left (these are usually called Module1, Module2 etc) and do the same for them. Double click to open and see if there's any code in there.