Excel 2003 Runtime error 40036

[Anoop V]

My company still uses the older MS Office 2003 everywhere, so I'm
stuck working with that. However, I seem to have come across a runtime
error which started coming up just today. Specifically, Runtime Error
40036 (Application-defined or object-defined error).
It seems to come up every time the compiler reaches a Worksheets
container in my code. So far, it's prevented me from using the lines:
Worksheets("Sheet1").Activate
or
Worksheets("Sheet1").Range("A1").Value = 4

or anything related to the container. Till yesterday, all of my code
had been running without any hitches, but starting from today morning
this error came up everywhere this container was used.
I've looked at other forums for help on this, and in each place it
appears this error seems to have come up for a random command or
object, even if the code had functioned properly before. Also the
solutions, where given, were always workarounds specific to that line
of code, and nothing that directly solved the problem, except for
reinstalling the entire MS Office, which I can't do due to Admin
rights. Any help?

 

[GS]

After serious thinking Anoop V wrote :

My company still uses the older MS Office 2003 everywhere, so I'm
stuck working with that. However, I seem to have come across a runtime
error which started coming up just today. Specifically, Runtime Error
40036 (Application-defined or object-defined error).
It seems to come up every time the compiler reaches a Worksheets
container in my code. So far, it's prevented me from using the lines:
Worksheets("Sheet1").Activate
or
Worksheets("Sheet1").Range("A1").Value = 4

or anything related to the container. Till yesterday, all of my code
had been running without any hitches, but starting from today morning
this error came up everywhere this container was used.
I've looked at other forums for help on this, and in each place it
appears this error seems to have come up for a random command or
object, even if the code had functioned properly before. Also the
solutions, where given, were always workarounds specific to that line
of code, and nothing that directly solved the problem, except for
reinstalling the entire MS Office, which I can't do due to Admin
rights. Any help?

Is there a sheet named "Sheet1" in the workbook? OR has it been
renamed, maybe?

Is the code in the same workbook that 'Sheet1' is in? If so...

ThisWorkbook.Sheets("Sheet1").Range("A1").Value = 4

...whereby it's not necessary to activate the sheet to assign a value.


Otherwise, you could ref the CodeName property in cases where sheet
tabs may be renamed by other users...

ThisWorkbook.Sheets(Get_SheetTabName(ThisWorkbook, _
"Sheet1")).Range("A1").Value = 4

Function Get_SheetTabName(Wkb As Workbook, CodeName As String) As
String
Dim Wks As Worksheet
For Each Wks In Wkb.Worksheets
If Wks.CodeName = CodeName Then _
Get_SheetTabName = Wks.name: Exit Function
Next
End Function

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Bob Flanagan]

My company still uses the older MS Office 2003 everywhere, so I'm
stuck working with that. However, I seem to have come across a runtime
error which started coming up just today. Specifically, Runtime Error
40036 (Application-defined or object-defined error).
It seems to come up every time the compiler reaches a Worksheets
container in my code. So far, it's prevented me from using the lines:
Worksheets("Sheet1").Activate
or
Worksheets("Sheet1").Range("A1").Value = 4

or anything related to the container. Till yesterday, all of my code
had been running without any hitches, but starting from today morning
this error came up everywhere this container was used.
I've looked at other forums for help on this, and in each place it
appears this error seems to have come up for a random command or
object, even if the code had functioned properly before. Also the
solutions, where given, were always workarounds specific to that line
of code, and nothing that directly solved the problem, except for
reinstalling the entire MS Office, which I can't do due to Admin
rights. Any help?

Another possibility is that the sheet has become protected.

Robert Flanagan
Add-ins.com LLC
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[anoopvargheese]

Another possibility is that the sheet has become protected.

Robert Flanagan
Add-ins.com LLC
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

No, the workbook was completely unchanged. It was working on Monday, and then on Tuesday it started causing the runtime error. There was no renaming of sheets or change in protection.

 

[GS]

Did you even try my suggestions? It uses 'fully qualified' refs and so
should not raise an error!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[anoopvargheese]

Did you even try my suggestions? It uses 'fully qualified' refs and so
should not raise an error!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

@Garry : Thanks for the suggestions, and I did try it out, but it's no joy.It's still giving the same error.
What I meant earlier was that it appears as if the Worksheets / Sheets collection isn't working in the first place. The problem definitely isn't with anyone modifying the contents of the file, as I'm the only one who has access to it. My own opinion is that some reference to the functions or properties under the Worksheets / Sheets is now missing or deleted in some way.

To explain it further, I can't even access any sheets using the sheet indexnumbers. Even the following line causes the same error
Worksheets(1).Activate
or
Sheets(1).Activate

for any index number.

 

[GS]

(e-mail address removed) brought next idea :

@Garry : Thanks for the suggestions, and I did try it out, but it's no joy.
It's still giving the same error. What I meant earlier was that it appears as
if the Worksheets / Sheets collection isn't working in the first place. The
problem definitely isn't with anyone modifying the contents of the file, as
I'm the only one who has access to it. My own opinion is that some reference
to the functions or properties under the Worksheets / Sheets is now missing
or deleted in some way.

To explain it further, I can't even access any sheets using the sheet index
numbers. Even the following line causes the same error Worksheets(1).Activate
or
Sheets(1).Activate

for any index number.

It sounds like you have a 'corrupt' file and so you might want to
rebuild the project starting with a new file and move components into
it one by one, testing to see if/when the issue appears as you go.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion