Solved

Application Defined (40036) Error when using Sheets("xxx").Select

Posted on 2002-07-05
8
1,030 Views
Last Modified: 2007-12-19
When I use:

Sheets("name").Select
or
Worksheets("name").Activate

I get a runtime error:
Application Defined or Object Defined Error (#40036)

What is wierd about this is that it is part of a loop that does the same steps to several different worksheets and uses a variable for the sheet name, for instance:

Function ProcessSheet(strSite as string)
   windows("windowname").activate
   sheets(strSite & "restofname").select
   'do some action on the sheet
end function

When it runs the FIRST TIME THROUGH it concatenates the sheet name with the 'restofname' part and runs just FINE.  The second call of the function causes the error listed above.

Is this a reference problem?  I am lost, please advise...

Cid



0
Comment
Question by:arthurcid
  • 5
  • 3
8 Comments
 
LVL 44

Expert Comment

by:bruintje
Comment Utility
Hi Arthurcid,

-this could a corrupted sheet in the book
-what happens when you insert a new sheet
-copy the contents of the problem sheet to the new one
-delete the second sheet that gives the problem
-and name the new sheet like the problem one

-is this sitll giving errors?
-or is there something different like the third sheet giving a problem?

HAGD:O)Bruintje
0
 

Author Comment

by:arthurcid
Comment Utility
No, it is not a corrupt sheet.

I have tried several different approaches (using newly created sheets, etc.).

Bottom line is this:

This :  sheets(var & "text.xls").activate

will NOT work if you do it TWICE in the same macro.  

In other words,

1.  Run that line (using "John" for var)
2.  Do a loop
3.  Run that line (using "Bill" for var)

I get application defined error every time it runs the SECOND time.  Hell, even during DEBUG MODE i can change it to :

sheets("Johntext.xls").activate
RIGHT IN THE MIDDLE OF DEBUGGING (WHILE THE CODE IS STILL RUNNING) and still get the error when I try to 'step' through it after the change.

I TOTALLY DONT UNDERSTAND WHY IT WORKS JUST FINE THE FIRST PASS THROUGH AND THEN ON SUBSEQUENT PASSES I GET THE ERROR.

Really pissing me off and nobody is replying to this, so I am about to give up.

I will GLADLY give several hundred MORE points for an valid answer to this question.

Cid
0
 

Author Comment

by:arthurcid
Comment Utility
No, it is not a corrupt sheet.

I have tried several different approaches (using newly created sheets, etc.).

Bottom line is this:

This :  sheets(var & "text.xls").activate

will NOT work if you do it TWICE in the same macro.  

In other words,

1.  Run that line (using "John" for var)
2.  Do a loop
3.  Run that line (using "Bill" for var)

I get application defined error every time it runs the SECOND time.  Hell, even during DEBUG MODE i can change it to :

sheets("Johntext.xls").activate
RIGHT IN THE MIDDLE OF DEBUGGING (WHILE THE CODE IS STILL RUNNING) and still get the error when I try to 'step' through it after the change.

I TOTALLY DONT UNDERSTAND WHY IT WORKS JUST FINE THE FIRST PASS THROUGH AND THEN ON SUBSEQUENT PASSES I GET THE ERROR.

Really pissing me off and nobody is replying to this, so I am about to give up.

I will GLADLY give several hundred MORE points for an valid answer to this question.

Cid
0
 

Author Comment

by:arthurcid
Comment Utility
No, it is not a corrupt sheet.  Same problem OVER AND OVER again even using sheets made from scratch.

THIS IS REALLY PISSING ME OFF, ID BE GLAD TO GIVE SEVERAL HUNDRED MORE POINTS FOR THIS IF SOMEONE COULD PROVIDE A CORRECT RESOLUTION.

0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:arthurcid
Comment Utility
No, it is not a corrupt sheet.  Same problem OVER AND OVER again even using sheets made from scratch.

THIS IS REALLY PISSING ME OFF, ID BE GLAD TO GIVE SEVERAL HUNDRED MORE POINTS FOR THIS IF SOMEONE COULD PROVIDE A CORRECT RESOLUTION.

0
 

Author Comment

by:arthurcid
Comment Utility
oops, sorry...

this: sheets(var & "text.xls").activate
should be
This :  sheets(var & "text").activate

and sorry for the double posts, my internet at work was being buggy so i couldnt tell if they were posting or not.
0
 
LVL 44

Accepted Solution

by:
bruintje earned 200 total points
Comment Utility
been some time but i was gone for a while from active participation in july

what i did was in a excel workbook tested this code

Option Explicit

Function ProcessSheet(strSite As String)
  Windows(1).Activate
  Sheets("Sheet" & strSite).Select
  'do some action on the sheet
End Function

Sub t()
Dim i As Integer
For i = 1 To 2
  ProcessSheet (i)
Next
End Sub

where sub t calls the processsheet function and it works for me, so what's done in the rest of your processing is there a sheet renamed or copied or something

see if the snippet from above works for you if it does then the error comes from later processing then the selection

:O)Bruintje
0
 
LVL 44

Expert Comment

by:bruintje
Comment Utility
Hello arthurcid

this question is open for more then 2 months
time to clean up
if not stated otherwise

my recom will be
-PAQ and no refund
-this will be finalized by an EE Moderator
-with no further update (28.09.2002)

PLEASE DO NOT ACCEPT THIS COMMENT AS ANSWER

HAGD:O)Bruintje
posted by ToolzEE v1.0
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Article by: dandraka
There are several quick shortcuts that can make your life easier in Microsoft Programs.  These simple tips and tricks will your work more productive and you faster at completing your tasks! MS Word (1) Creating Re-usable Scraps You can create s…
How many times recently have you prepared a presentation or emailed a document to a client and you have found that they have older versions of MS Office and they can not open the file you have prepared.  Although most visitors to this site are exper…
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now