Excel Workbook_Open Macro freezes

[piker42]

I have a simple statement in my Workbook_Open macro "Sheets("GIS Labor Supplier Spending").Select" which works fine if I open a single work book or if I open a number of files with this macro from Windows Explorer.

However, if I open a number of files at the same time from the Excel Open Files dialog box, I get the following error message on some variable number of the files being opened:
- Run-time error '1004'
Select method of Worksheet class failed.

If I follow the debug statement, it goes directly to the select statement. Checking the immediate window for the active sheeet, active workbook, comparing the worksheet name in the select statement to the name Excel returns, all produce no errors. The same worksheet will open fine by itself or when opened from Windows Explorer.

Anyone with any idea as to why this might be happening?

 

[firefytr]

Can you post your open workbook code? Are you using multi-select?

-----------
Regards,
Zack Barresse

 

[rmikesmith]

Here's something to try quickly:

[b]ThisWorkbook.[/b]Sheets("GIS Labor Supplier Spending").Select

Regards,
Mike

 

[piker42]

My original code was as follows:

Private Sub Workbook_Open()
Sheets("GIS Labor Supplier Spending").Select
End Sub

I tried rmikesmith's suggestion using 'ThisWorkbook.Sheets...' and it seems to have reduced the number of 1004 errors by over half.

Would having the Excel workbooks saved under different versions of Excel cause this problem? I'm running Excel 2003, but the users may be using 2000, 2002, or 2003.

 

[rmikesmith]

Change Select to Activate. This eliminated the intermittent error message for me.


Regards,
Mike

 

[piker42]

Thanks, Mike. It seems to have worked but since I only have access to the latest version of Excel and had to save everything using it, I'm not sure what will happen next month when the users submit there workbooks saved in older versions. However, I'm going to update all the workbooks and the template with the new code and see what happens.

 

[rmikesmith]

Good Luck, piker42


Regards,
Mike

 

[firefytr]

No, older versions do not matter. There is a big difference between Select and Activate. Always explicitly set your objects.

-----------
Regards,
Zack Barresse