I've been beating my head against the wall on this one for a couple weeks now and have finally decided (I'm stubborn, I am) that I'm not going to resolve this one myself. The specific error is "Method 'Insert" of object 'Range" failed" although it has also manifested as "Automation error The object invoked has disconnected from its clients." While I've searched the various forums, none of the reported instances seem to match this case.

I have a fairly complex workbook (attached) which is used by project managers in my company to estimate the initial project and to track project financial progress. The Workbook has evolved over many years and through more hands than mine for the macros provided to manipulate the spreadsheet. In its current incarnation, there are 20 worksheets, some of which are routinely hidden from the user. We are finally creating a comprehensive regression test for this workbook and this error was discovered during testing prior to deployment.

To use the workbook, the initial estimate is entered using either the Worksheet Estimate sheet or the MPP Estimate sheet. Various project parameters are entered on one of these sheets including Region & Currency. The failing case is to change the Region & Currency (cell H3) on the Worksheet Estimate sheet, select the Project Overview sheet and click the "Add PCR" button at the top. This seems to fail every time. Another failing case is to change the Estimation Method (cell B14) on the Project Overview sheet from "Worksheet" to "MPP" and click the "Add PCR" button. Both the Region & Currency and Estimation Method are selected from a drop down list using data validation and run no code, so it's extremely difficult to think of any reason this should affect the operation of the macro, and it also fails when a value in a cell without data validation is changed (for example, Project Manager on Worksheet Estimate (cell C8). Passing cases include:
  • Open the workbook, select the Project Overview sheet and click the "Add PCR" button. This works reliably.
  • After changing the Region & Currency on the Worksheet Estimate sheet, use the button to Add Task Rows, then select Project Overview and click Add PCR.
  • After changing the Estimation Method on the Project Overview sheet, select the MPP Estimate sheet and use the button to Insert Estimated Labor Rows, then select Project Overview and click Add PCR.
Finally, I've tested the failing case on prior versions and they are also failing - whether they have always been failing or not I can't say. Our company has a policy of automatic updates for Windows and Office, so all users should have the same version/service pack of both Windows 7 and Office 2010, and we have seen the failure on at least 2 separate computers. Note: this isn't a normal use case for the workbook since a PCR (Project Change Request) would ordinarily be added long after all the other parameters are set and rows have been added for the original estimate, but if we've found it once I can guarantee one of the other users will find it eventually.

Any suggestions will be welcomed!
Regards,
Pat Klaus
Project Workbook Template-Clean.zip