Results 1 to 10 of 10

Thread: VBA Excel Run-time error '1004': Application-defined or object-defined error

  1. #1

    Thread Starter
    New Member
    Join Date
    Jan 2012
    Posts
    3

    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

  2. #2
    Junior Member
    Join Date
    Dec 2011
    Posts
    21

    Re: VBA Excel Run-time error '1004': Application-defined or object-defined error

    Can you specify which references you have enabled at the moment?

  3. #3
    Junior Member
    Join Date
    Dec 2011
    Posts
    21

    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.

  4. #4

    Thread Starter
    New Member
    Join Date
    Jan 2012
    Posts
    3

    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

  5. #5

    Thread Starter
    New Member
    Join Date
    Jan 2012
    Posts
    3

    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

  6. #6
    I don't do your homework! opus's Avatar
    Join Date
    Jun 2000
    Location
    Good Old Europe
    Posts
    3,863

    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!

  7. #7
    Junior Member
    Join Date
    Dec 2011
    Posts
    21

    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

  8. #8
    Hyperactive Member
    Join Date
    May 2006
    Posts
    365

    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

  9. #9
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  10. #10
    Junior Member
    Join Date
    Dec 2011
    Posts
    21

    Re: VBA Excel Run-time error '1004': Application-defined or object-defined error

    Quote Originally Posted by opus View Post
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width