Resolved: Method ‘Sheets’ of Object ‘_Global’ Failed

Category: MS ExcelTags:

We have a complex macro enabled spreadsheet. Today we got a ticket from the business user that Excel is throwing error:

“Run-time 1004 error: Method ‘Sheets’ of object ‘_Global’ failed.”.

I could not find an answer even after searching online for hours. None of the online discussion was giving the root cause of the error. Everyone were discussing how to change the code to fix the error.

The spreadsheet was working fine for couple of years but it suddenly stopped working. I suspected that it wasn’t the code but it was something else. I needed the root cause of this error.

So What is “Sheets” of Object “_Global”?

Finally, I found the root cause of this error at this link. Author discusses that Sheets is shortcut of Application.ActiveWorkbook.Sheets.

Now the error makes sense. In simple words, this error is trying to say that “Sheets” is trying to resolve to “Application.ActiveWorkbook.Sheets” but it encountered “Nothing” in the object chain.

So either “Application = Nothing” or “Application.ActiveWorkbook = Nothing”. As “Application” would always be available, the main culprit for this error is “Application.ActiveWorkbook”. Value of “Application.ActiveWorkbook” is “Nothing” and hence method “Application.ActiveWorkbook.Sheets” is not available which is the root cause of this error.

Why Application.ActiveWorkbook Becomes Nothing

Application.ActiveWorkbook represents currently open, visible and focused Excel spreadsheet.

Here are few reasons why Application.ActiveWorkbook becomes Nothing and hence it throws an error in VBA code.

  • Excel file (i.e. active workbook) is in PROTECTED VIEW mode.
  • Excel file (i.e. active workbook) is hidden.
  • Excel file (i.e. active workbook) looses focus.

3 Solutions to Resolve This Issue

You may encounter one of the above issues. Depending on your issue, try following solutions depending on your situation.

  • If Excel file is in PROTECTED VIEW, make sure to make it editable.
  • If the Excel file is not visible, go to View > Windows > Unhide and make sure to unhide your Excel file.
  • If for any reason your Excel file looses focus, make sure it is visible and the focus is on your current Excel file.

Solution For My Issue

In my case, VBA code was executing upon opening the file. I temporarily commented out the code which was throwing an error. Once the code executed, it still did not show the Excel file.

In Developer Mode, I could see the file is open but it was not visible. I figure out that somehow Excel file was accidentally hidden by the user.

I went to View Ribbon > Window > Unhide and unhide the Excel spreadsheet.

Once the file is visible, it successfully executed the code.

Share

13 comments

Your email address will not be published. Required fields are marked *

  1. m says:

    Thank you so so so much ! I was stuck and couldn’t find any clear answer.

  2. Kamal Hossain says:

    Your suggested solution did help me. Although the excel file opened the worksheet was not visible. By using View > Window > Unhide I could access the worksheet.
    Many thanks indeed

  3. Janet Kwamboka says:

    Hello, My excel shows run time error 1004 method worksheet of object global failed. Kindly assist with the solution.

    1. Hi Janet,

      Try all the solutions available on the post and check if any one of it applies to your situation.

      Regards,
      Vishal Monpara

  4. Peter Mwangi says:

    Hi my excel worksheet shows Run-time error 1004
    The password you supplied is not correct. Verify that the CAPS LOCK key is off and be sure to use the correct capitalization. This occur when am validating my work. Please help. windows 10

  5. Julio says:

    Muchas gracias, llevaba muhcho tiempo batallando con este error

  6. Jorge Cerol says:

    You saved me. I Spent lots of time because de same code was working with other sheets. Now it’s OK. Thanks

  7. Rob Sands says:

    Thanks this saved me a lot of time

  8. Chilli says:

    You will see it more frequently now, since the same thing happens if you don’t have enabled editing, before you start the macro.

  9. simon says:

    Thanks for this. In my case it turned out the excel was opened in protected view inadvertently. I closed it – reopened it and it worked. Your solution told me straight away where it was coming from .

    Cheers

  10. mohamed says:

    thanks thanks thanks very much.

  11. Sandra says:

    Thank you so much!

  12. Joseph says:

    THANKS FOR SHARING