Excel Freezes

Any OnChange Event Code is there in your workbook?

If possible can you please share the file or screenshot for better understanding?

Seems to be a nice design to see but unable to fix anything from the provided screenshot.

If possible can you please share a workbook?

Please attach a sample workbook with enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Note: Please don't attach documents containing confidential data like (address, telephone, ID#s, etc.).

Nothing seems to be wrong in your file and I suspect that the calculation of the formula's are taking more time.

May be in your original file you may be having more sheets with many formula's. At the same time your file is xlsx file only so there will not be any macros.

I suggest you to put the calculation mode to Manual and Press F9 whenever you need to calculate the workbook. At the same time save the file in .xlsb format which will reduce the file size considerably and work faster too..

Try my post # 6 suggestion and raise your further questions if the suggestion fails.

Don't know what else I have to suggest Because the file is not having any macro codes and turning the calculation to Manual should work actually.

Hi Zach,

I had to repair my 2010 - I suggest that you repair your 2010.

I think the conditional format is the problem
I did not count them all but ik think there are more than 1000!!

Did you consider my suggestion?

If they're all "Not Responding" then I'd suspect that you need to repair them all - you could try one

It worked for me!

Yeah, I would venture to say that the number of conditional formats are a serious problem in your example file. It's locking up my machine just trying to view them... Ironically, I built a schedule system for work that ended up having a similar problem. I put it together back in 2003 with that version of office, and back then, conditional formatting applied to ranges would stick to the range if any insertion or deletion or rows or columns within the range happened, but after 2010 came out, I converted the file to an .xlsm, and it slowly got worse and worse performance. I eventually figured out that this was because the number of conditional formats had grown exponentially. (The way this schedule system functioned was by inserting and deleting rows to add new schedule sections for departments, and individual lines for employees, etc.) What was happening was that the conditional format that spread across a range would get split up into 3 separate conditional formats for 3 separate ranges if you inserted a row in the middle of the original range. (A very obnoxious change...) Anyway, I got around this by removing all conditional formats and then dynamically re-applying them with VBA every time one of these operations took place. Now the system works flawlessly. In fact, I took the time to remove all of your conditional formatting, and the workbook performs totally normally now. I used the following code:

Please Login or Register  to view this content.

You should try applying this to your whole workbook and see if it makes a difference to test this theory. Writing the code to re-apply conditional formatting dynamically was difficult and interesting code to write, but it definitely got the job done.

Also, a potential method of addressing your concern of protecting the original workbook from users in public with access to it is to have the workbook print to a pdf file every time it saves so that the public (employees, I assume) can still view the necessary information, but then the people responsible for the integrity of the original workbook will be the only people who ever have access to it, while the information will still be made available to those who need it. (That's what I did) To avoid potential file access errors, you do have to make the file print to a pdf, then have individual files on workstations that copy that pdf to their local drive and open it from there instead of opening the original pdf file.

I reckon you'd be better off using code to apply the formatting colors to the cells instead of using all those cf formulas

Originally Posted by JosephP

I reckon you'd be better off using code to apply the formatting colors to the cells instead of using all those cf formulas

In may case, using code to apply conditional formatting was necessary because the conditional formatting was formula based and was set up so that no matter what (using if(or(cell="",cell<>""),true,false) ) the cell or range would be formatted a particular way, including non-locked cells. The people responsible for writing the schedule are not so familiar with excel, and they did need to be able to format some central groups of cells by adding color to the background or text, which meant that sheet protection had to enable this. There is no way to enable color changes for parts of a sheet via protection, other than setting a cell as unlocked, so this way the formatting integrity of the workbook's layout could be preserved no matter how uneducated the user was and they could still make the changes they needed where they were allowed to.