-
Jan 26th, 2012, 06:23 PM
#1
Thread Starter
New Member
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
-
Jan 26th, 2012, 06:53 PM
#2
Junior Member
Re: VBA Excel Run-time error '1004': Application-defined or object-defined error
Can you specify which references you have enabled at the moment?
-
Jan 26th, 2012, 06:55 PM
#3
Junior Member
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.
-
Jan 26th, 2012, 07:36 PM
#4
Thread Starter
New Member
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
-
Jan 26th, 2012, 08:10 PM
#5
Thread Starter
New Member
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
-
Jan 27th, 2012, 12:29 AM
#6
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?
You're welcome to rate this post!
If your problem is solved, please use the Mark thread as resolved button
Wait, I'm too old to hurry!
-
Jan 27th, 2012, 12:28 PM
#7
Junior Member
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
-
Jan 27th, 2012, 07:27 PM
#8
Hyperactive Member
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
-
Jan 31st, 2012, 01:23 PM
#9
Junior Member
Re: VBA Excel Run-time error '1004': Application-defined or object-defined error
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.
-
Jan 28th, 2012, 03:32 AM
#10
Re: VBA Excel Run-time error '1004': Application-defined or object-defined error
I'm siding with Opus in the error being caused by the worksheet "01" not being located inside the workbook object.
different error
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|