This post is inspired by the previous post Excel Humor #9 – You do not see me
Situation
Suppose you are going to share an Excel workbook to your colleagues. However there is a worksheet with sensitive data that you don’t want them to see. So you hide the worksheet. As you know Excel well, you further Protect Workbook, with password set, in order to avoid user from unhiding the worksheet.
Since you are also a careful person, you test to make sure no one can unhide the worksheet unless one knows the password to unprotect workbook:
Now you are pretty sure that no one can unhide the worksheet, without the password.
YES! BUT… ARE YOU SURE NO ONE CAN SEE THE DATA ON IT?
Here’s a simple trick to look into the data on the hidden sheet. Indeed I don’t even need to unhide the worksheet to get the data on it.
Did you notice that the name of the hidden worksheet was disclosed in the VB Editor? That’s the window you saw after clicking “View Code” in the above screencast.
From there, I know that the Hidden Sheet is called “Secret“.
With this little piece of information, anyone can retrieve the data on any cell on the worksheet easily.
A picture tells thousand words; a sceencast tells even more 🙂
See?! What I did was just a simple formula of referencing:
=Secret!A1
Secret No More!!!
Conclusion
Please don’t get me wrong. I am not encouraging you to hack into data that is not intended for you. My point here is:
DO NOT include sensitive/confidential/private data in a workbook at all, if the recipients are not supposed to read that.
wmfexcel 
also you can hack the worksheet.xml in 7zip :
– open the xlsx/zlsm inside 7zip as an archive
– find the file xl/workbook.xml
– edit this file and replace the text state=”hidden”
done
LikeLike
dear my master??
can every help me, i forgot a password on excel / personal.xls (open workbook or excel file)
how to solve my problem using vba from another workbook?
LikeLike
hi friend, I have problem with excel sheet.
If I send the XL sheet then can you help me to un hide all hidden sheet?
Thanks in advance.
LikeLike
Hi Karthi,
Have at look. It should help.
LikeLike
Fantastic. You told the way to hack. Also guide us, how to make them really inaccessible/invisible even thru formula.
LikeLike
Thanks. Nevertheless it’s not about how to hack into hidden sheet… but keep in mind that Excel is not a secure way to hide secrets…. 😅
LikeLike
Hi, I recently did something like this when I inherited a protected workbook full of hidden sheets. I was even able to resurrect a bunch of charts, by cycling through them with VBA.
LikeLike
Yes! VBA could do much more! 🙂
LikeLike
Awesome trick,please do keep sharing your brilliant ideas they are always…… impressive!!
LikeLike
You are welcome Saba. Thanks for your kind words. 😊
LikeLike
Hi,
If you define the Sheet’s “VISIBLE” Property to: xlSheetVeryHidden, and you Password protect your VBAProject – you make it much more difficult to break into the VB Editor in order to see the Hidden sheets name.
*** There are a few ways to crack into a Protected VBA Projects (I’m not going to share them here).
Michael (Micky) Avidan
LikeLike
Hi Micky,
Yes but… there are many ways to list all worksheets’ name with simple codes in Immediate Window. 🙂
LikeLike
Hi, Wong,
I am realy confused – and will be glad to learn how to show/activate the “Immediate window” under/within a Password Protected VBAProject ?
Michael (Micky) Avidan
LikeLike
Hi Micky,
Thanks for your question.
I am not good in VBA… I thought I can still use Immediate Window in VBE… I tried to password protect the VBA project using Excel 2011 for Mac… and it worked…
So what i am thinking is… 1) i didn’t protect the VBA project properly, 2) it works differently in Mac. Probably 1) 😅
Let me try on a windows machine later.
Other thought: can we use a new workbook and its Immediate Window to get the worksheet name of another workbook? 🤔
Have a nice weekend! Cheers
LikeLike
Hi Micky,
I just tried in Excel 2013. It works too. In the VBE, go to View –> Immediate Window. or simply Ctrl+G (if the Immediate Window is not visible). I can see and work with the Immediate Window as usual. Moreover, I can open an new workbook and use VBA to get all worksheets’ name (hidden, very hidden, or visible) of another workbook successfully. Having said that, as concluded in my blogpost, it’s not about how to get the data on a hidden sheet. It’s about NOT to consider “hiding” something in a workbook as a secure way to protect data from being seen.
Side topic: I found sth strange in my trial with the VBE… When I tried to lock the VBProject by right-clicking it, Properties, Protection, Lock from Viewing, passwords… OK. Saved the file. Reopened it. Then the password is GONE…. NO protection at all. Then I tried again and saved it as xlsm. Still didn’t work. Then I inserted a blank module to the VBProject, then it worked…… Strange?
As I said, I am not good in VBA, maybe I missed something that I don’t know. ~_~
LikeLike
Hi, Wong,
1) In order to password protect and maitain a VBAProject you must have at least one Macro in it.
Try:
Sub Hi()
MsgBox “Hi…”
End sub
2) I still don’t get to the bottom of how to present/open the Immediate window in a Protected(!) VBAProject.
Could you explain in details ? (Step-by-step, please)
Have an nice weekend,
Micky
LikeLike
Hi Micky,
Are we talking about the same “Window”? What I meant was the “Immediate Window”, that can be activated in the VBE.
View –> Immediate Window
(Shortcut: Ctrl+G)
To my understand, “Immediate Window” does not belong to any specific VBAProject, so that can be opened regardless of password protection status.
LikeLike
Who Needs VBA to read the sheet names? Even if they are VeryHidden? Nobody Needs to 🙂
Write into one cell this -> =’sheet1′!A1
and Excel will ask you to open the document you are trying to reference to. Open the specific document and Excel will ask you to select the sheet. And yes in this list are also the veryhidden-ones.
LikeLike
Wow……. this is a nice trick 👍🏻
LikeLike