Solved

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

Posted on 2002-07-05
8
1,084 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
SendBlaster Pro 4 - Bulk Email Sending Software

SendBlaster 4 Pro - Best Bulk Emailing Sending Software
Automatic Subscribe / Unsubscribe Processing
Great for Newsletters & Mass Mailings
Optional HTML & Text Composition
Integration with Google Features
Built in Spam Score Checking
Free Professional Templates - Feature Packed!

 

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
 

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

752 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