Workbooks, subscript out of range

[olssonj]

Hi!

I have a delicate and (in my world) irrational problem. I hope you can helt
me understand the situation.

I recently changed from Office 2003 (XP) to Office 2007 (Windows Vista). I
therefore have Excel 2003 workbooks and Office 2007 (.xlsx, .xlsm) workbooks.

Now my little problem:

For a long time I have used the simple command:

Workbooks("book1.xls").Activate or
Windows("book1.xls").Activate

and it always worked out fine. But, now in my new Office 2007 workbooks this
wont work anymore (Error 9, subscript out of range). But, if I open my old
2003 Excel Workbooks with the same command the code works just fine.

I followed all the threads that discusses this matter and I have tried
everything, such as:

1. Change "book1.xls" to "book1" to "book.xlsx" etc.
2. Changed my windows settings to show/not show .xls for known file types
3. Using a short VBA code that shows the name of the workbook in a message
box just to make sure I have used the right name
4. Used a saved workbook and unsaved workbook
5. Used different computers with Office 2007 with the same error (Error 9,
subscript out of range)

The Workbook that the code suppose to activate will always be open before
the user run the macro.

As I undestand it it has to be a setting question or a reference question.
Or what do you think? It looks so easy, what am I missing???

Thank you for your time and help!!

//Jonas

 

[olssonj]

Hi again!

Some times it helps just to post a qestion here...

My bad, I missed to activate the right worksheet. In Office 2003 I had the
setting of just one worksheet in a new workbook and in Office 2007 it is 3 by
default. SO EASY! SO FRUSTRATING!

See you next time!

//Jonas