Resolved: Method ‘Sheets’ of object ‘_Global’ failed

By | September 27, 2016 | 4 Comments
Category: MS Excel Tags: ,

We have a complex macro enabled spreadsheet. Today we got a ticket from the business user that Excel is throwing 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.

Finally, I found the root cause of this error at this link. Author discusses that Sheets is shortcut of Application.ActiveWorkbook.Sheets. For whatsoever reason, if the current sheet looses focus, the Application.ActiveWorkbook becomes null and code throws this error.

I temporarily commented out the code which was causing an issue. Worksheet was not visible, even though the it was open. Somehow the workbook was hidden. Everything worked as expected once I unhide the workbook from View > Window > Unhide.


About Vishal Monpara

Vishal Monpara is a full stack Solution Developer/Architect with 13 years of experience primarily using Microsoft stack. He is currently working in Retail industry and moving 1's and 0's from geographically dispersed hard disks to geographically dispersed user leveraging geographically dispersed team members.

4 thoughts on “Resolved: Method ‘Sheets’ of object ‘_Global’ failed

  1. simon

    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





Leave a Reply

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