Solved

Excel: Don't allow user to unhide sheets

Posted on 2010-11-07
5
478 Views
Last Modified: 2012-05-10
I would like to make some hidden worksheets in an Excel file. How do i make it so that a user can't unhide my hidden worksheets?
0
Comment
Question by:ouestque
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 7

Expert Comment

by:Brad Sims, CCNA
ID: 34081249
I have used the xlSheetVeryHidden property in VBA before and that worked for me.  Do you know how to accomplish this or would you like me to post a sample?
0
 
LVL 7

Accepted Solution

by:
Brad Sims, CCNA earned 167 total points
ID: 34081271
Just change the sheet name below in quotes.

Sub Test()
Worksheets("Sheet1").Visible = xlVeryHidden

End Sub
0
 
LVL 50

Assisted Solution

by:Dave Brett
Dave Brett earned 167 total points
ID: 34081272
In short there is no foolproof method to enforce the protection.

You have two options
- VeryHidden (as per comment above)
- Protecting the workbook structure

Both are easily defeated, the first with basic VBA knowledge, the second by trivial code that cracks the password. The sructure protection being a little harder to crak in my view

That said you should use both together.

Cheers

Dave
0
 
LVL 7

Expert Comment

by:Brad Sims, CCNA
ID: 34081313
Dave is correct.  I should have prefaced my first statement by saying the solution worked for me because my users have no VBA knowledge (other than recording macros).  
0
 
LVL 23

Assisted Solution

by:Michael Fowler
Michael Fowler earned 166 total points
ID: 34081421
ouestque

1. In the workbook select ALT+F11 to open visual basic editor
2. On the left select the worksheet to be hidden
3. If the properties window is not visible select "View/Properties Window"
4. In the Visible field of the properties window change the value to "2 - xlSheetVeryHidden"
5. Select Tools/VBAProject Properties
6. On the Protection tab sellect "Lock project for viewing" and enter a password

While this is not perfect it will stop the casual user
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone 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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

623 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