I have found a workaround to the Excel VBA “ run time error 40036 application-defined or object error”. It does not fix the original problem, which is in excel, SOMEWHERE.
A few responses I have seen have shown a simple program which could not select a screen, or cell on one. Here is a simpler one ….. 1 line.
“nsgbox ActiveSheet.Name”
Test this on the screen giving the problem. It will fail.
Test it on another screen. It works, unless the other one is a problem.
The workaround is simple but may not be complete.
1) Rename the tab with the screen name
2) Create a new tab
3) Copy the old screen contents to the new one.
4) Rename the new one to the original name.
5) Do not forget to copy formats and the screen vba objects Sheet text.
That is it.
The workaround leaves a couple of unanswered questions.
A bit of speculation. Excel seems to have lost the connection of the Screen name to the screen number. Using screen numbers may be better, but are too confusing.
I have had other inconsistent problems with the screen, which may be related. For instance, a known error when trying to hide columns. Worse yet the “on error xxxxx “ command does not work. I have not yet tried that on the new screen. It may be an answer to that problem also.
If none of this works and Excel is totally screwed up, as it sometimes is my other “General Solution” is worth trying. It is also worth trying if you have a program you have been working on for a while and has a lot of redone or deleted sections, and is BIG.
1) Open a new file
2) Create the same tabs
3) Copy over all pages and scripts
4) Avoid copying objects. Recreate them (Copying should not be a major issue if there are lot of objects)
5) DEBUG