Workbooks take WAY too long to open (usually)

heyjude1971

New Member
Joined
Nov 11, 2009
Messages
5
Hello All:

I can find my answer on this forum 99% of the time (thanks!), but have a couple of issues for which I can find no solution.

I'm using Windows XP with Excel 2003.

I have a master workbook which contains about a dozen sheets with no more than 300 rows and a dozen columns used on each.
I have several employee workbooks which link to this master to get most of their data.
The master gets some of it's data from the editable cells in the employee files.
- Thus the master links to all employee files and each employee file links to the master.

I've had this setup for awhile with no issues. Recently I added a small userform to each employee workbook and a couple of more sheets similar to the existing ones. Since then, two things have been occurring:

(1) 90% of the time when I try to open any two of these files at the same time, each workbook after the first one takes several minutes to open.
(2) Whenever I open the master file, it tells me I have bad links.

If I close Excel completely and open any one file, it opens quickly.
Until my recent changes, I was able to open all files at once, and each of them opened quickly. -Occasionally this still happens, but 90% of the time the subsequent files I open after the first one take way too long to open.

All of these workbooks have the same named ranges on the same sheets.
All of the employee workbooks have the same small userform using the same name.

These files are all < 3 MB in size.

I have a macro which runs when a workbook opens, but the speed issue is still there when I remove the code.

I didn't do anything with the new sheets I added that wasn't already done with the existing (problem-free) workbooks. Possibly two more sheets were too many? (This seems unlikely.) Possibly adding the small userform caused an issue? Maybe the timing with my update was coincidental.

My other issue is that I get the error message saying I have bad links every time I open the master file. I've tried several things to fix this:
When I opt to correct the links, the status shows that all linked files are OK. I've tried linking them to a different workbook, then selecting the same workbook.
I've tried updating all the links.
I deleted ALL unused rows and columns.
I've searched through the defined names for any names with #REF (even the hidden ones).
I have no embedded objects in any of these files.
If I copy any sheet into a new workbook, it has the same issue.

All the data I'm linking to appears to be linking correctly and I can find no unintentional links or references.

Does anyone know anything else I can try to get rid of the link error message?
Even more importantly, I need to be able to open more than one of these files at a time without waiting for so long.

I would greatly appreciate any input.
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

heyjude1971

New Member
Joined
Nov 11, 2009
Messages
5
Update: I just tried removing the userform from one of the employee workbooks and it still takes a VERY long time to open if I already have the master workbook open.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,993
Messages
5,622,046
Members
415,875
Latest member
Tarali

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top