Excel hangs and fails but only next day, this does involve formulas and macros....

Hi all, long time reader first time poster.... firstly a big thank you to all experts who have posted on here, with out your helping of others, I would never have been able to develop my spreadsheet.

I have a very interesting situation that has just recently developed with my Spread Sheet.... ok workbook contains many forms macros and cell formulas, where the problem is I cannot tell but I will explain what is happening and hopefully someone could give me a clue to narrow it down...

I start wb from a "Template level" import and manipulate data, print to PDF, update another "Masterfile" wb and save close wb to lets call it "Client wb", in 4 days time "Masterfile" sends email with link for to me to open "Client wb" and contact them, make notes and close again.... as soon as anything is written to the wb, whether by macro or manually, cpu usage goes 100% and thats the end of that.... however if I do the same process BUT open the "Client wb" same day as it was created there is no issue... so tests as such... open same day no issue, next day - fail, 2 days - fail and so on, also only happens with autocalc on, open a sheet that failed turned it off and calculated manually with F9... no problem so is only failing with autocalc on, unfortunately has to be on though. Now the wb macros run fine as a form appears first up, user then types notes into form textbox and selects save&close button next form asks if contact was made and yes or no selected and wb updates & closes... well should... and has been.... now not

So what changes in the wb after a certain period? n.b. there are no Now() functions or Date() type functions in the cell formulas but plenty of Worksheets("......").Range("....") = Date type macros....

Any help appreciated....

Sorry about the preiods.
Yes looping I understand but why no loop when in manual calc?
Code writes to external wb's and imports from external text files and access db's, connections are being closed and this only occurs once at the intial loading of data the first time the wb is used.
Yes other wb's are on same server and our computers are rarely shut down.
Am using the autocalc off and application.calc method at the moment and have not yet seen a problem but this is not really appropriate.
Unfortunately I cannot upload a copy of the wb as there is privelidged and personal information in it and I am also a bit embarrased at how some of the code is written as I started as a newbie developing this 2 years ago.
I cannot understand how code is a problem if the problem only exists when autocalc is on.... I just wish there was a way I could step through the autocalc process calculation by calculation and find the problem....

Thanks for your help Nate.

Yes definately so, as soon as any section of code writes anything to any cell (I have tested this by changing the code to write to different cells/sheets) the stall occurs.

If I use any code that does not require anything to be written to the sheet/cells then the code continues through, saves and shuts down without problem, even if I turn off Macros all together but leave autocalc on and type anything into a cell... any cell... it stalls. I have even removed all macros and forms completely on a "broken" wb and tried typing into a cell and yes stall....

Just deleting the first sheet caused stall... obviously cells reference other sheets, I'll switch autocalc off and continue trying...

Ok so I have narrowed it down to 2 sheets, if either of these 2 are deleted autocalc is no longer a problem. But, these are the 2 main sheets, one is data where all the file data is stored for macros and formulas and such and the other is the Main Cost Calculator which as the name suggests does all the calculating.... there are thousands of formulas on these 2 sheets... do I just look for the ones that refernce each others sheet maybe?

ok so after much stuffing around the above formulae are not the culprits.... even with the wb switched to manual calc I still get a freeze! Now though I can use ctrl + break to break into the macro, select debug, and simply hit the play button again and off it will go... (debug highlighted line is always at the "End Sub" line and code will run until the next "Calculate" line... break in to macro... hit play... and no probs again til next calculate...??

This is really bugging me out!!?? Anyone got any ideas?? Pease??

oh also set application calculation settings allow iterations and max iterations as 1.... no difference.

Hi wannabe,

I see your location is in the same part of the world as mine (well country anyway) so you will certainly understand what I mean when I say "I feel like I am shadow boxing in the dark" as I read the thread

It sounds like you are going to have to invest the time in creating a sample version of your workbook. obviously this will take some time and you will need to sanitise the data to protect personal information etc.

Once you have done that, prove to yourself that the same problem exists in the sample workbook and then post it on the forum.

I fear that without that we are very unlikely to randomly stick the tail in the problem donkey

Jmac1947

jmac, i certainly do know what you mean...
ok sanitised versions attached...
First is blank template and second is data imported....
Works today but wil it tomorrow??

just to confirm, the stuart hayes workbook works today (I added in a not manually and it seems still to be OK

Your issue is that if I alter the note from today any time after today it will just grab 100% cpu and freeze

Thats right, tomorrow, change any cell ... lets say on the Main Cost Calculator sheet... and yes will freeze. I hope it doesn't make a liar of me....

yes works today, but will stall tomorrow with autocalc on or off....

Ok... made a liar of me... back to the drawing board. When I can replicate the issue I will attache a new file... my apologies