Bizarre Problem

[MrExcel]

This is a bizarre problem. I have an application that was originally written with dialog sheets and old style controls. We've replaced just about everything.

Now, occassionally, when I open the file, I get this:
Run time error 57121
Application-Defined or object-defined error.

I click Debug, it is a line of code that is trying to Select Sheet 1. Here is the really bizarre part....

In the debug window, the entire Project Explorer window is greyed out. It is as if Excel hasn't finished opening the file yet, so it doesn't realize that Sheet1 exists.

Further complication - I get this error on my VB6 development machine, but not the vanilla Excel machine.

Any thoughts, ideas, anything would be appreciated.

Bill

 

[Mark O'Brien]

I have had the grey screen appear many times during break down of code. Have you turned off screenupdating? That was usually the cause of the grey screen for me and once the bug was fixed it was ok.

Have you tried testing for sheet1 before selecting it? I would just slap in this function that Ivan posted t'other day with a msgbox for the result:

<pre>
Function IsWorkSheet(strName As String) As Boolean
Dim Ws As Worksheet

IsWorkSheet = True
On Error Resume Next
Set Ws = ThisWorkbook.Sheets(strName)
If Err Then IsWorkSheet = False

End Function</pre>


taken from this thread:

http://216.92.17.166/board/viewtopic.php?topic=25191&forum=2

 

[MrExcel]

Thanks Mark.

I added a 2 second delay with .WaitTime to Auto_open to allow the thing to fully open, and it works. Bizarre. You wouldn't think you would have to do that...

Bill

 

[Mark O'Brien]

I just hope no one uses a much slower PC than you.

 

[bg18461]

Resurrecting this thread from the dead...

I have a similar problem with Excel 2007. I have this spreadsheet that uses VBA forms to manipulate data entry and create reports. At first the program ran fine on my local pc. At then put it on sharepoint and for the most part it also ran fine. Now I copied a local version back to my desktop and I get the 57121 runtime error. It is strange because I know how to avoid it but I don't quite understand why.

When I first open the excel file and run the program I get the error when it tries to select a sheet in the vba code. When I click on debug I get this image in VBA Editor

If you notice in the explorer view pane, the sheets are in blue and do not show the "name of the sheet". If I close the excel file, reopen it, go into the VBA editor before I execute the code that changes the sheet I get this in the explorer view pane:

Which is normal. If i close out the vba editor and execute the code now, it runs fine. This happens each and every time I do this. I also tried the wait statement, but that did not work, any ideas?

 

[pins123]

I had this error message "57121" after upgrading to Excel 2007 from Excel 2003. The workbook worked fine in 2003.

The problem was that the trust centre had defaulted to "disallow all active X contols with no warning". To fix, go into the Excel 2007 Trust Centre, Click Active X and set to a security setting you are comfortable with.

 

[mickmac]

Yeah, just experienced this today. Programmed on 2003, worked fine. then co-worker opened and saved on 2007, and it's all messed up, 57212 errors. Can't save it, can't view some macros (says... invalid data type), sheet buttons don't work. Will try to see if settings as you mentioned are the problem.

 

[mickmac]

I opened it , in 2003, and this time selected End instead of Debug when 57121 error appeared, and it seems to work. I was able to save it, then re-opened, this time waited 5 sec before selecting 'enable macros', and no error. How do you add a time delay? Saw somewhere about using Application.Wait ......

 

[Skysurfer]

Re: Bizarre Problem (error 57121 on workbook open)

Hi,

I just encountered this problem with a 2003 file and was able to resolve it by opening and saving the file in xl2010. Then, when re-opened in xl2003, it worked fine.

The source of this problem (at least in my case) seemed to be ActiveX controls located on a sheet I wanted to open on launch, using Workbook_Open. Any way I would reference the sheet in question (Sheet1, Sheets(1), Sheets("Welcome")), I would get the 57121 error. And from within xl2003, the only way to resolve it was to not have any ActiveX controls on Sheet1...which was not acceptable.

So, along comes xl2010 to save the day. Hopefully, for good!

Lawrence

 

[Skysurfer]

UPDATE:

Apparently, working in the VB editor in xl2003 revives the error, and the xl2010 procedure of opening-saving resolves it until the file is again used in the xl2003 VB editor.

The good news so far is that if building an Excel "application", it is unlikely the user will open the file in the xl2003 VB editor and trigger the error. <crossing fingers>

~L