Super Hidden
Sandra Grady
So, I bought an SSD to upgrade the speed of my PC, and started transferring extranneous files over to a 10TB portable USB drive that I had. In this effort to reduce the size of my hard drive before trying to clone it to the SSD to make it bootable, I used the program SpaceSniffer to see if there's any other places on my hard drive that I could pull data from to reduce the size further, and to make my new SSD as "clean" as possible before the cloning.
Which is actually another "clone" of an even older terabyte hard drive that I'd upgraded from previously, and completely forgot about. Here's the thing though, I already have "Show Hidden Files and Folders" checked, and I'm able to see hidden files and folders. However, I am NOT able to see this folder for some reason:
Has anyone ever heard of this before, or can explain what's going on here? Also note the "read only" checkbox having a square in it, and the hidden checkbox being checked AND disabled in the very first screenshot above.
This seems to be a "super" hidden folder for some reason. And having used Windows since the 3.1 days, I've NEVER seen anything like this before. So, do any bigger Windows experts have any explanation for the strange behavior of this folder?
After research, I found out that Robocopy has a bug where it altersthe attributes of the directory to a system and hidden file. It's beenknown for quite some time and there are numerous blogs and questionsout there on the subject. In order to reverse this change I had toalter the attribute so that the folder should no longer be marked aseither a system or a hidden file. These attributes are altered in cmdusing the parameters 's' and 'h' respectively
If you're clever and don't want to deal with the problem in the firstplace (as it's a known bug with Robocopy), when you write out yourRobocopy line in cmd, include the minus attributes (/A-:SH) at the endof your command. This will stop the destination folder from beinghidden and from being turned into a system file, thus preventing theproblem ever happening in the first place:
The code that creates item IDs for files reads the file attributes and records them for future reference. It is this code that checks for the leading tilde and if found internally sets the FILE_ATTRIBUTE_SYSTEM flag on the item it created. This is what causes hidden files beginning with a tilde to be treated as super-hidden.
Another thing to note is that you cannot change the visible properties of the activeWorksheet. To make the change go back to the Workbook and select a sheet you want visible to make that the activeWorksheet. Then access the BBC and select the sheet you want to hide from the project explorer and change its properties.
Diogo,
Your macro looks good and should work; my best guess is that the sheet name is different than the sheet name in the macro, ie, Sheet1. One pretty easy way to determine the sheet name is to first make the sheet Hidden instead of VeryHidden. Then, start the macro recorder and then unhide the sheet. Then stop the recorder and examine the sheet name used by the macro recorder by checking out the VBA code that is generated. Then, use that sheet reference in your Unhide macro and you should be good to go!
Hope this helps!
Thanks
Jeff
Leah,
If a sheet is hidden, rather than very hidden, a user will know because the Unhide command is enabled and if selected will display the Unhide dialog which contains a list of all hidden sheets. To my knowledge, there is no visual indication in the standard Excel user interface for very hidden sheets.
Thanks
Jeff
thanks Jeff for the very informative article and comments, i received a workbook from the world bank, the table of content indicates that there are four worksheets for assumption and projection, but they are not on the tab,so I was familiar with hidden property,but they are not hidden. I searched and I found your article,so I have from your article that there are also a very hidden property, I checked it via VBA, the worksheets are not there, do you think is there any way they are hidden in different, or they just delete them so not share their assumptions.
I have also another question,when re saved the same file received from the World Bank,with the different name, a message came up indicating that some of the format will be lost, when I clicked okay,i noticed all worksheet of graphs are lost, how can such thing happens.
Thank you for the article. When I set the worksheet.visible property to xlVeryHidden, it breaks my code that accesses a range that I address as the rowsource property for a listbox in a form. How do I address the range once the sheet is veryhidden?
Are you exasperated because you cannot find the spreadsheet one of your formulas refers to? The sheet does not appear among other tabs at the bottom of your workbook, nor does it show up in the Unhide dialog box. Where on earth could that sheet be? Simply, it is very hidden.
Unhiding a sheet that was hidden normally is very easy. All you have to do is right-click any visible worksheet, click Unhide, and select the sheet you want to view. Very hidden sheets are a different story. If the workbook contains only very hidden sheets, you won't even be able to open the Unhide dialog box because the Unhide command will be disabled. If the workbook contains both hidden and very hidden sheets, the Unhide dialog will be available, but very hidden sheets won't be listed there.
From the user's perspective, what is the difference between hidden and very hidden sheets? It is simply this: a very hidden sheet cannot be made visible via the Excel user interface, the only way to unhide it is with VBA. So, if you want to make some of your worksheets much more difficult to unhide by others (e.g. those containing sensitive information or intermediate formulas), apply this higher level of sheet hiding and make them very hidden.
How to make Excel worksheets very hiddenAs already mentioned, the only way to make a sheet very hidden is by using the Visual Basic Editor. Depending on how many sheets you want to hide, you can proceed with one of the following methods.
That's it! As soon the Visible property is changed, the corresponding sheet tab will disappear from the bottom of your workbook. Repeat the above steps for other sheets if necessary and close the Visual Basic Editor window when finished.
Make active worksheet very hidden with VBA codeIf you have to hide sheets on a regular basis and are annoyed about having to do it manually, you can automate the job with a single line of code. Here's the macro that makes an active worksheet very hidden:
If you are writing a macro for other users, you may want to take care of situations when a workbook contains only one visible sheet. As you may remember, it's not possible to hide absolutely all worksheets in an Excel file (whether you are making them hidden or very hidden), at least one sheet should remain in view. So, to warn your users about this limitation, wrap the above macro in an On Error block like this:
Sub VeryHiddenActiveSheet() On Error GoTo ErrorHandler ActiveSheet.Visible = xlSheetVeryHidden Exit Sub ErrorHandler: MsgBox "A workbook must contain at least one visible worksheet.", vbOKOnly, "Unable to Hide Worksheet"End Sub
Make multiple worksheets very hidden with VBA codeIn case you want to set all selected sheets to be very hidden, go through all of the selected sheets in an active workbook (ActiveWindow) one by one and change their Visible property to xlSheetVeryHidden.
Sub VeryHiddenSelectedSheets() Dim wks As Worksheet On Error GoTo ErrorHandler For Each wks In ActiveWindow.SelectedSheets wks.Visible = xlSheetVeryHidden Next Exit Sub ErrorHandler: MsgBox "A workbook must contain at least one visible worksheet.", vbOKOnly, "Unable to Hide Worksheets"End Sub
How to unhide very hidden sheets in ExcelNow that you know how to completely hide sheets in Excel, it's time to talk about how you can view very hidden sheets.
Sub UnhideAllSheets() Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets wks.Visible = xlSheetVisible Next wksEnd Sub
How to use Very Hidden Sheets macrosTo insert any of the above macros in your Excel workbook, perform these usual steps:
For example, here's how you can make all of the selected worksheets very hidden:
I hope this short tutorial has shed some light on Excel's very hidden sheets. I thank you for reading and hope to see you on our blog next week!
Hello
Thanks for sharing valuable tips
I dowloaded a free spreadsheet on the web. I have been looking for the code in vain.
How to find the code out and go trough it fr needed changement pls
I HAD ONE WORKBOOK THAT CONTAINS 23 SHEETS IN IT AND I SAVED THAT DATA BEFORE I LEFT THE OFFICE DAY BEFORE YESTERDAY. NEXT DAY WHEN I OPEN THAT TO START WORK ON IT AGAIN IT SHOWING ONLY 11 SHEETS OTHER SHEETS ARE NOT SHOWING
Here is what I did. I had my xlsx file open.
I wanted to unhide the entire worksheet (there was only 1 worksheet in this workbook)
I clicked on the upper left hand corner of the sheet to highlight the entire worksheet
I clicked on View & then Hide (I'm not sure why as I was trying to UN-hide a few hidden rows.
Then the file shut down and closed.
I found the file but cannot open it at all.
I try VBX (Alt-F11) but would not open the excel file reporting an error 'could not be loaded'.
Now what? Any help is appreciated. I can find the file but cannot open it.
Part 2 - When I try to change the name of the file it says 'File In Use, close the file and try again'. But No way is it open unless it is so hidden I can't see it?
Thanks Dan
Great work.
I tried to unhide a worksheet using your visual basic editor version.
When I try to change from 0-xlSheetHidden to -1-xlSheetVisible i get the error
"Unable to set the visible property of the Worksheet class"
I recognize that all right mouse click function are greyed out.
Is there a way to sort this