Solved

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

Posted on 2002-07-05
8
1,051 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
ID: 7134110
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
ID: 7138921
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
ID: 7138923
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
ID: 7138926
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:arthurcid
ID: 7138928
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
ID: 7139416
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
ID: 7268872
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
ID: 7295834
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

920 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

12 Experts available now in Live!

Get 1:1 Help Now