prompting to save workbook every time (even when no changes made)

  • Thread starter Thread starter neowok
  • Start date Start date
N

neowok

i have 3 workbooks all very similar and every single time i open any o
them, even when not changing anything, i am prompted to save th
thing.

its so bad that i can literally open the workbook then click the clos
button straight away and it STILL prompts me to save it because i
seems to think something has changed.

as far as i can see i dont have any macros or anything that will ru
every time it starts and change something (i did have a = today in on
cell which kept the date as today but taking this out made n
difference
 
Hi Neowok!

You probably have volatile functions in the workbooks:

Some of Excel’s functions are obviously volatile: RAND(), NOW(),
TODAY()



Others are less obviously volatile: OFFSET(), CELL(), INDIRECT(),
INFO() (although the CELL("Filename") function is not volatile)



Some are volatile in some versions of Excel but not in others:
INDEX()became non-volatile in Excel 97.



A number of functions that are documented by Microsoft as volatile do
not actually seem to be volatile when tested:



INDEX(), ROWS(), COLUMNS(), AREAS()



You can download volatileFuncs.zip for a test workbook that shows if a
function is volatile from Charles Williams:



http://www.decisionmodels.com/calcsecretsi.htm



--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
i do have some vb code attached to a couple of buttons and a combobox
but this code isnt executed until the user clicks one of the buttons so
i assume shouldnt be causing the problem.

For Each cell In Me.Range("mytablesdisplay").Columns(1).Cells
If Not cell.EntireRow.Hidden Then
viscnt = viscnt + 1
End If
Next cell

thats part of the code so i am using cell(). also using autofilter but
again this isnt used until the user clicks one of the buttons.

other than that im not using any of those things within cells in the
workbook, its all vb code that isnt touched until the user does
something, but maybe that doesnt matter?
 
Actually, Norman was referring to the worksheet function: =cell()

Like:

=cell("filename",a1)

Do you have any of those worksheet functions that Norman described?
 
no, the problem is the listfillrange of the combobox as i describe
above (edited my post in between your reply it seems). unless this i
blank, im getting prompted to save the file every time its opened, eve
if its just opened then closed again without touching it. if its blan
then i get no prompts to save unless i actually change something.

must be some way around it, maybe only fill the combobox after it
clicked or something, which in theory should solve it right
 
You're not touching the combobox? And you're not touching the autofilter?

Do you have any auto_open or workbook_open code that may make excel think your
workbook is dirty (needs to be saved)?

Was your workbook developed in a version prior to xl2002 and you're using xl2002
or higher?

Xl2002 (and xl2003???) likes to recalculate any older version workbooks.

If none of this applies, any worksheet_activate code?

Maybe sprinkling some:

msgbox thisworkbook.saved

throughout your code will give a hint of any macro that's changing the .saved
property.

When it changes to False, you're close.

If it stays true, then you shouldn't be getting this message. (yeah, I don't
have any other guesses if none of this helps.)
 
not touching anything, it was doing it when opening the workbook the
closing it again without touching it.

as soon as i blanked the 'listfillrange' in the combobox, the proble
vanished.

So now ive had to write some code that only fills in listrillrange whe
the user clicks on the combobox arrow to open it, and then blank
listfillrange again when the box loses focus.

i dont have any code in thisworkbook or workshee
 
Back
Top