VBA Excel Run-time error '1004': Application-defined or object-defined error
Hi guys,
I've tried a simple code like:
Worksheets("01").Cells(8, 56).Interior.ColorIndex = 3
but I got the following error message:
"Run-time error '1004': Application-defined or object-defined error"
I guess I need to set a specific library reference to get it working, but if it is the reason, I don't know which one. But maybe there is another reason, it is only a guess.
This code is in a worksheet module.
Does anyone have any idea about how to get it working?
Thanks
Re: VBA Excel Run-time error '1004': Application-defined or object-defined error
Can you specify which references you have enabled at the moment?
Re: VBA Excel Run-time error '1004': Application-defined or object-defined error
Make sure that the Microsoft Excel 12.0/14 object library is checked.
Re: VBA Excel Run-time error '1004': Application-defined or object-defined error
Hi dovbah,
Thanks for replying.
Currently I have enabled the following references:
Visual Basic For Application
Microsoft Excel 12.0 Object Library
OLE Automation
Microsoft Office 12.0 Object Library
Microsoft Forms 2.0 Object Library
Microsoft ActiveX Data Objects 2.8 Library
Microsoft ActiveX Data Objects Recordset 2.8 Library
Thanks
Re: VBA Excel Run-time error '1004': Application-defined or object-defined error
One more information:
For the sample below, the first line works and shows:
The Current Color is 20
but the second line raises the mentioned error, what means that I can read this cell property but for some reason I can not set a new value, as if this property was read only.
MsgBox "The Current Color is " & ActiveSheet.Cells(8, 56).Interior.ColorIndex
ActiveSheet.Cells(8, 56).Interior.ColorIndex = 3
Thanks
Re: VBA Excel Run-time error '1004': Application-defined or object-defined error
The differnce between your codes is using "ActiveSheet." (working) and "Worksheets("01")." (not working). Are you sure a Worksheet named "01" does exist?
Re: VBA Excel Run-time error '1004': Application-defined or object-defined error
There is nothing wrong with your code and you have enabled all the proper references. This leads me to believe that the cell is Locked and the sheet is protected.
Unprotect the sheet and test the code.
You can still have a locked cell and make this work by enabling "format cell" option when you protect the sheet.
Hopefully this fixes your help
Bah, Dov
Application Developer
OCTA
Re: VBA Excel Run-time error '1004': Application-defined or object-defined error
Hello,
I'm siding with Opus in the error being caused by the worksheet "01" not being located inside the workbook object.
Kind regards
Steve
Re: VBA Excel Run-time error '1004': Application-defined or object-defined error
Quote:
I'm siding with Opus in the error being caused by the worksheet "01" not being located inside the workbook object.
different error
Re: VBA Excel Run-time error '1004': Application-defined or object-defined error
Quote:
Originally Posted by
opus
The differnce between your codes is using "ActiveSheet." (working) and "Worksheets("01")." (not working). Are you sure a Worksheet named "01" does exist?
He does change the code to ActiveSheet when he posted the code again. He still gets the same error.
The fact that he is able to read and not write to the cell makes it look like the cell is read only.