Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Can't view sheets even after unhiding all sheets.....VeryHidden???

Posted on 2009-12-24
6
Medium Priority
?
1,398 Views
Last Modified: 2012-05-08
HI,

Someone has mailed me an excel file. In the file I can see that he has given rerefence to a number os sheets but I can't view them. I tried to unhide all sheets, but excel says that there are no hidden sheets. When I am looking at the VBA Project list, I can see a lot of sheets which are just not apprearing in the Sheet Tabs even though there aren't any hidden cells. In the vba project I can see that he has used the below mentioned code. The sheets mentioned in the code are the ones which I can't view. I wan't to how to do that and how to undo that.
Private Sub Workbook_Open()

Dim x As Date
Dim y As Date
x = Date
y = #2/23/2010#
If x >= y Then
Sheets("Master").Visible = xlVeryHidden

Sheets("TL Data").Visible = xlVeryHidden
Sheets("ES").Visible = xlVeryHidden
Sheets("Project").Visible = xlVeryHidden
Sheets("TS").Visible = xlVeryHidden
Sheets("TS Report").Visible = xlVeryHidden
Sheets("ES Report").Visible = xlVeryHidden
Sheets("Project Report").Visible = xlVeryHidden
Sheets("2COMS").Visible = xlVeryHidden
ActiveWorkbook.Save


Else

End If
End Sub

Open in new window

0
Comment
Question by:ghoshsaikat83
  • 3
  • 3
6 Comments
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 2000 total points
ID: 26117975
Sheets can be hidden at two levels: hidden and very hidden. Hidden sheets can be unhidden by choosing the menu command Format->Sheet->Unhide, selecting the sheet to unhide from the list, and clicking OK. If that menu command is disabled then either there are no hidden sheets, there are hidden sheets but they are very hidden, or the workbook structure is protected. To unprotect the workbook structure, choose the menu command Tools->Protection->Unprotect Workbook. A password may be needed to complete the unprotection. Once unprotected try to unhide the hidden sheet again.

If the hidden sheet is very hidden, press ALT+F11 to open the VBE, press CTRL+R to open the Project Explorer window, locate and select the sheet to unhide in the list of project objects, press F4 to open the Properties window, locate the Visible property and change it to "-1 - xlSheetVisible". Press ALT+F11 to return to Excel.

Kevin
0
 
LVL 5

Author Comment

by:ghoshsaikat83
ID: 26117997
Kevin,

I always knew you are a lliving legend....but this was the first time I had the pleasure to raise a question which was solved by you....I don't have words to thank you.....YOU ARE GREAT. :)

Joydip (Big Fan of yours)
0
 
LVL 5

Author Comment

by:ghoshsaikat83
ID: 26118006
Just out of curiosity....What does the code do??? Does it automatically change the viewing status of those sheets based upon some date???

Joydip
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26118014
:-)

That wonderful comment was just added to my list of "fan mail":

http://www.experts-exchange.com/M_1677072.html

Kevin
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26118020
>What does the code do??? Does it automatically change the viewing status of those sheets based upon some date???

Yes. If today (Date) is on or after 2/23/2010 then the sheets are hidden. Which is odd because today really isn't on or after 2/23/2010.

Kevin
0
 
LVL 5

Author Comment

by:ghoshsaikat83
ID: 26118058
Okay, Thanks a million for the solution and the explanation Kevin. May God bless you...

Joydip
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question