I consider myself a decent VBA programmer, but I've finally come u
against an error that I can't debug in its simplest form.

I keep getting an Error 40036 Application - Defined or Object - Define
error.

I've traced and debugged until I came up with the simplest of script
that generates the error message:

Sub test()
'
' test Macro
' Macro recorded 2/3/2004 by Rob
'

'
Sheets("DETAIL BOOK REPORT").Select

End Sub

I've tried to change the name of the tab, but the error remains.
This is the 4th tab in a spreadsheet containing 8 tabs.

Is there some kind of bug in Excel (I'm using the XP edition) that thi
error occurs in certain situations?

Should I be looking for something in the spreadsheet that's causin
this?

I can run the same macro on the other tabs and it's fine - no error.

It's obviously not in the code.

Any help would be greatly appreciated as this is killing one of m
scripts which is being used by people at my company on a daily basis.

Thanks,.

Ro

--
Message posted from http://www.ExcelForum.com

Crazy. The only thing I could offer is to confirmed in the Exce
Project Objects that there is a sheet name that matches your shee
exactly. The not defined, as you know, is saying that there is not
sheet called that.....from my understanding

--
Message posted from http://www.ExcelForum.com

Ro

A couple of thoughts

1) Scroll through all the sheets in the workbook and output the names on another workbook somewhere (for each sh in workbook sh.name). Then use each of the sheet names output and see if you can select them. If you can, then look for a special character in the name (do the Code function)

2) If this doesn't work, then use the sheets(index) method of selecting the sheet. It is a workaround that will allow the macro to continue in the short term

Ton

----- shinydiamond wrote: ----

I consider myself a decent VBA programmer, but I've finally come u
against an error that I can't debug in its simplest form

I keep getting an Error 40036 Application - Defined or Object - Define
error

I've traced and debugged until I came up with the simplest of script
that generates the error message

Sub test(

' test Macr
' Macro recorded 2/3/2004 by Ro



Sheets("DETAIL BOOK REPORT").Selec

End Su

I've tried to change the name of the tab, but the error remains
This is the 4th tab in a spreadsheet containing 8 tabs

Is there some kind of bug in Excel (I'm using the XP edition) that thi
error occurs in certain situations

Should I be looking for something in the spreadsheet that's causin
this

I can run the same macro on the other tabs and it's fine - no error

It's obviously not in the code

Any help would be greatly appreciated as this is killing one of m
scripts which is being used by people at my company on a daily basis

Thanks,

Ro


--
Message posted from http://www.ExcelForum.com

Thanks for both of your suggestions.

I checked out the project objects as jpendegraft suggested.
The sheets are named correctly there. What I did notice is that th
sheet I'm trying to select is named as sheet1, when in fact it i
actually sheet5 (it's also preceded by a hidden sheet4).

So, I tried to renumber and re-order the sheets, but that doesn't work
It still gives the same error when trying to select that same sheet n
matter what the number.

Next I tried ~x's suggetions.
I output the names of all the sheets to the debug window and everythin
looks fine. (this is actually how I discovered the hidden sheets).

Then I tried indexing by sheet# as he suggested...

Sub test()
Sheets(5).Select
End Sub

And the same error message.....


Starting to feel a bit frustrated....Either this is really simple o
it's some exotic Microsoft bug.....really need to come up with
workaround....any other ideas?

Thanks for your help

Ro

--
Message posted from http://www.ExcelForum.com

Rob

One more thought. Drag the sheet into a new workbook. You can then close the original file without saving and the sheet will still be in it.

With the sheet in a new workbook, see if you can select it there. Try changing the name if that doesn't work. Also, try cycling through each of the sheets in the new workbook and selecting the sheet, then printing out the names using the msgbox command. That way you will actually be on the sheet when the name occurs.

If that doesn't work, go back to the original file, make a copy of the sheet, delete the original sheet, rename the copy as per the original, and see if that will work. Make sure that you can select the new sheet at each stage of the process and if you get to the stage where you can't select it, you will at least know where it is falling down.


Tony

----- shinydiamond wrote: -----

Thanks for both of your suggestions.

I checked out the project objects as jpendegraft suggested.
The sheets are named correctly there. What I did notice is that the
sheet I'm trying to select is named as sheet1, when in fact it is
actually sheet5 (it's also preceded by a hidden sheet4).

So, I tried to renumber and re-order the sheets, but that doesn't work.
It still gives the same error when trying to select that same sheet no
matter what the number.

Next I tried ~x's suggetions.
I output the names of all the sheets to the debug window and everything
looks fine. (this is actually how I discovered the hidden sheets).

Then I tried indexing by sheet# as he suggested...

Sub test()
Sheets(5).Select
End Sub

And the same error message.....


Starting to feel a bit frustrated....Either this is really simple or
it's some exotic Microsoft bug.....really need to come up with a
workaround....any other ideas?

Thanks for your help

Rob


---
Message posted from http://www.ExcelForum.com/

You could try looking at the code behind the sheet you are attempting to select or activate. I have seen individuals block sheet access via select or activate by entering a "With ws end with" to block access via code.