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

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.



14 responses to “Resolved: Method ‘Sheets’ of Object ‘_Global’ Failed”

  1. Cam Peneff Avatar
    Cam Peneff

    Excellent – thanks so much. The hidden file was the personal.xlb, and i would prefer to leave it hidden. is there a solution that would allow the file to save, close, and then hide the personal.xlsb. I suppose i would then have to embed some code in the system to hide the personal.xlsb on open?

  2. m Avatar

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

  3. Kamal Hossain Avatar
    Kamal Hossain

    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

  4. Janet Kwamboka Avatar
    Janet Kwamboka

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

    1. Vishal Monpara Avatar
      Vishal Monpara

      Hi Janet,

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

      Vishal Monpara

  5. Peter Mwangi Avatar
    Peter Mwangi

    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

  6. Julio Avatar

    Muchas gracias, llevaba muhcho tiempo batallando con este error

  7. Jorge Cerol Avatar
    Jorge Cerol

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

  8. Rob Sands Avatar
    Rob Sands

    Thanks this saved me a lot of time

  9. Chilli Avatar

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

  10. simon Avatar

    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 .


  11. mohamed Avatar

    thanks thanks thanks very much.

  12. Sandra Avatar

    Thank you so much!

  13. Joseph Avatar


Leave a Reply

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