Last week I heard from a customer who was trying to unhide a worksheet. When he right-clicked on the worksheet tabs, the Unhide command was greyed out. The reason was that someone had set the Visible status of the workbook to VeryHidden. This can be set manually in the VBA Editor or by using VBA code.
Switch to the VBA Environment
Click the Developer tab on The Ribbon
Click Visual Basic
If The Developer Tab is not displayed (1)…
Excel 2010/2013: Right click in the empty area to the right of The Ribbon (1), select Customize the Ribbon (2)
If The Developer Tab is not displayed (2)…
Tick the Developer checkbox
Note: Excel 2007 – click the Office Button (big round one at the top left), click the Excel Options button at the bottom and in the first section (Popular), tick Show Developer Tab in Ribbon
Ensure Project Explorer Window & Properties Window are displayed
The left hand side of the screen should look something like ths:
If Project Explorer Window or Properties Window are not displayed…
Click View on the menu and select Project Explorer and/or Properties Window
Unhide the worksheet
In the Project Explorer, click on the worksheet that you want to unhide (1)
Click the Visible drop-down (2) and select xlSheetVisible
Exit from the VBA Editor, switch back to Excel and the worksheet should now be displayed
i tried the above procedure and in the last step, im getting error as ” Unable to set the Visible property of the worksheet class”
Hi Alfred. I’m guessing you only have 1 worksheet in the file and you were trying to hide that sheet? You must have at least one sheet visible.
Great!Save my time!Thanks a lot.
Hi, I also experienced Alfred’s issue. There are multiple visible sheets in the workbook, the file just seems pretty buggy. If I right-click on the sheet in the VBAProject window, it allows me to hide the already hidden sheet. If I look at the visible state in the Properties tab, it tells me that the sheet is hidden and does not allow me to switch the class to visible. It’s a bit of a conundrum.
You cannot unhide a worksheet if a workbook is protected. You will get the error that Alfred saw. Use a password breaker if necessary.
My Workbook is not protected and i have multiple sheets that are visible and multiple sheets that are hidden. The hidden sheets are not very hidden just hidden. The hide / unhide options are faded out . I am using excel 16
My Workbook is not protected and i have multiple sheets that are visible and multiple sheets that are hidden. The hidden sheets are not very hidden just hidden. The hide / unhide options are not even appearing as an option when I right click on my mouse. I have office 365.
Appreciate any assistance.
Protect, then unprotect the workbook if none of the above worked
In order to unhide a sheet that was in a file that originally was protected, you may have to:
– Change the file extension to .zip (if it was project.xlsx, simply change to project.zip)
– Open the zip file.
– Expand the “xl” folder
– Extract file workbook.xml
– Open workbook.xml with Notepad
– Find
– when you find the line above, delete the command state=”hidden”
– Save the changes
– Place the workbook.xml file back on the zip file
– Rename the zip back to the original file extension.
– Enjoy!
Windows 365; Home/format/visibility/hide & unhide has “unhide sheet” grayed out. I’ve tried all of the above, including Protect & unprotect workbook. What’s next?
I was able to fix this error by protecting the workbook and instead of ignoring the option to choose a password I picked one.
YOU WILL NOT BE ABLE TO UNPROTECT OTHERWISE. At this point you can choose unprotect and place the password you selected back in. The unhide option will be visible again and you can view the hidden tabs.
I FORGOT MY PASSWORD. I REMOVED PASSWORD BY VBA CODE COPYED FROM NET.
NOW I AM UNABLE TO UNHIDE THE TWO SHEETS.