Error 40036 when activating a sheet

[shinydiamond]

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

 

[jpendegraft]

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

 

[Guest]

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

 

[shinydiamond]

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

 

[Guest]

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

 

[Guest]

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.

 

[pilotter]

I had this problem when converting a program from excel 2003 to excel 2007. It turns out that there was really a "compile" error in the sheet. I had "dim sort as string" up top as a sheet variable. 2007 did nmot like this, but with 2003 there was no problem. You should check your code -- especially the module level vars to see if you have anything that may be existing already. (I just renamed the var to mySort and then it worked fine)

 

[Sgtoconnor]

This is how I fixed it:

I had the same issue, and google led me to this Thread. I had the error message on the first Sheet that I tried it on titled "Budget", so I didn't know that the specific sheet was the issue.
After realizing that, I selected everything on the problem sheet, and pasted the content into a new sheet. I renamed it "test" and it worked, so I deleted the problem sheet, renamed the new sheet to "Budget", and dragged it the location I had wanted it on the sheet bar at the bottom of the page.

Hope this solution helps others!

-Patrick

 

[Luiggi]

Hi, now 28/07/2016 and I have same error: Error 40036 when activating a sheet
Is a very strange BUG from Excel, I think so. I can't find solution on internet and I have two files exactly equal and the other one hasn't this problem. Corrupt file? Maybe if we use that files in different versions of Excel can make that error... I don't know. Someone can explain? My Activex objects on sheet now are like "images"... ?? you select it, and is a image!! not a object! Ty.