Worksheet keeps crashing - is it coding issue or too many preparation formulae

[chrisjames25]

HI ALL

Over the last few months you all have helped me no end to get a finished workbook that my place of work can use to order transport, calculate the pallet configurations for the order and creat a despatch sheet.

THis was my first attempt at vba and everytime I appear to finish my project I encounter the problem that it crashes the next time I open the file and will then not reopen. I am not sure whether this is due to my coding being too basic and whether I could apply the same code across several sheets as opposed to writing it on each week I require transport. Or alternatively I use a lot of formulae with =indirect function in. I have read on here that that can cause issues.

Any suggestions wud be massively appreciated as I have spent so many hours trying to complete this and really do not want to fail at the last hurdle. I have attached a slimmed down version of my spreadsheet that contains only half the number of weeks.

Basically the ws works as follows:

1) index sheets allows you to select which week you want to do the transport for. THis makes that sheet visible and you then can jump to that sheet.

2) Opperman and browns sheets are a summary of the other sheets costings.

3) Each of the Week Sheets (that are hidden until chosen on index sheet) have a drop down menu to select the day of the week. ONce day is selected hiudden rows are revealed. Following inputting that days data you click onto the transport and despatch buttons at top to take you to the relevant sheet. Print these sheets and job done for day.

Apologies for rubbish explanation but once in the ws you will see it makes a lot more sense than I am currently making.

Grrr file is too large to load on this site as it is 2.5mb. Does anyone know what the limit for size is?

If you require anything further please let me know.

[AndyLitch]

I think it's 1Mb

[chrisjames25]

Hi again

I have been reading some other threads and one suggested cause is that I am using office excel 64bit. Has anyone else encountered this issue and if so has a cure been found?

[Norie]

Have you investigated why/where it's crashing?

Perhaps you could set up some breakpoints in the code (F9) and step through it with F8 to set what's happening.

Are there any error messages when Excel 'crashes'?

Is there anything in particular in the code, or in the worksheets, that you think might be the cause of the problem?

Perhaps you could post some examples of the formulas and code you have.

PS Unless you are using Windows API then using 64-bit shouldn't be a problem.

[chrisjames25]

Sadly I cant investigate why/where it is crashing as the minute I click enable macros at start up it crashes and box appears stating:

"excel is trying to recover your information, please wait this may take several minutes" and just hangs for ages and then closes file.

The code is probably a bit cumbersome but nothing massively complex in there. Each of the Week sheets has the following code including in

Please Login or Register  to view this content.

Repeat this code on every worksheet. WHen I have less than 5 ws with this code on all seems well but when I go above that and do a reopen it seems to crash.

[Norie]

Is there code that is triggered when the workbook opens, straight after macros are enabled?

What happens if you don't enable macros straight away?

[chrisjames25]

Hi Norie.

If I do not enable macros then the file does not crash.

Unless I have accidentally triggered something to fire straight away I do not believe any macros are triggered when opening the document.

[Norie]

Can you create a stripped down version of the workbook, eg remove data/formatting, and attach that?


PS You almost got the code tags right, the closing tag is [/code].

[chrisjames25]

So close with the code tags. Will get right next time promise!!!

HEre is a stripped down version of the ws. When it opens on the index tab from the drop down combobox only choose week 4 as that is the only sheet that still exists. Once in there you will be able to have a play and see what the code above does.

Many thanks

[Norie]

You do seem to have a lot of formulas INDIRECT

Is there a reason for that?

Is it because some of the weekly sheets don't exist yet?

How many sheets like 'Browns' and 'Opperman' do you have?

Do they all have so many formulas?

PS You could go back and edit the closing tag.

[chrisjames25]

The Opperman browns and index sheets are the only ones of those type I need. So admittedly my use of indirect function in those was lazy of me and purely used to avoid me linking each of the different week sheets to those.

The indirect function in the transport and despatch sheets are required I believe. Because the plan is to duplicate week 4 for each week of year so 1-52. And when u click on the transport or despatch command buttons the week number of the current sheet is inserted into a1 of the transport sheet and the rest of the data on that sheet is then linked via this indirect function.

Here is the code for changing the week number on that tab

Please Login or Register  to view this content.

If you copied week 4 and renamed it week 5 and changed the cell at top of sheet to week 5 as well you will hopefully see what the above rumblings mean.

Will go back and edit for u

[Norie]

Chris

Are the formulas used to get data from other worksheets to create summarys?

Do they need to be dynamic?

What I wondering is if it's possible to just copy data rather than use formulas.

Say for example in week 6 append the relevant data to the relevant place in the relevant worksheet.

By the way, I've got a kind of 'theory' why the workbook is crashing.

The 'theory' is that when the file is opened, after you've enabled macros, all the formulas are being calculated and that's what's causing the problem.

Just a 'theory' though.

[chrisjames25]

THe formulas in the opperman and browns sheet are used to get a summary of other sheets so I can amend them to not be indirect functions easily enough.

We have 6 computers at work and this morning I have tested the complete file on all the computers. The only one that crashes is mine and I am the only one running office 64 bit so im starting to think that it may be the cause of the issue.

THis article seems to suggest there is an issue with 64 bit office as well. http://social.technet.microsoft.com/...-9d1d46e7975f/

Any thoughts.

Perhaps I should do a reinstall of office and see if that removes issue.