Solved

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

Posted on 2002-07-05
8
1,107 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
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

635 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