|
Excel 2003 Runtime error 40036
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? |
Excel 2003 Runtime error 40036
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 |
Excel 2003 Runtime error 40036
On Wednesday, June 13, 2012 2:14:02 AM UTC-4, 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? 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 |
Excel 2003 Runtime error 40036
On Thursday, June 14, 2012 10:47:13 AM UTC+8, Bob Flanagan wrote:
On Wednesday, June 13, 2012 2:14:02 AM UTC-4, 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? 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. |
Excel 2003 Runtime error 40036
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 |
Excel 2003 Runtime error 40036
On Thursday, June 14, 2012 1:06:18 PM UTC+8, GS wrote:
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 index numbers. Even the following line causes the same error Worksheets(1).Activate or Sheets(1).Activate for any index number. |
| All times are GMT +1. The time now is 09:40 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com