Protect Hidden Sheet in Excel

Posted on 2014-04-25
Medium Priority
Last Modified: 2014-04-25
I have a worksheet that pulls data (using external data) from a SQL database.  In the same worksheet I have a sheet that pull data from a Access database.  The SQL sheet has confidential info on it that I want to be able to hide the data or sheet from anyone being able to see it.  On the Access sheet I have a Vlookup that is pull info from the sheet I want hidden, but when I hide and protect it won't let me refresh all because it say the data is Read Only.  Is there anyway to get the results I want.
Question by:nursecore
  • 2

Accepted Solution

Steve earned 2000 total points
ID: 40023653
You may have to make a Unprotect, unhide, refresh, protect, hide routine instead of just the refresh.

Sub GetRefresh()

Worksheets("Sheet1").Unprotect Password:="pswd"
Worksheets("Sheet1").Visible = xlSheetVisible

'**Refresh code goes here

Worksheets("Sheet1").Protect Password:="pswd"
Worksheets("Sheet1").Visible = xlSheetVeryHidden

End Sub

Open in new window


Author Closing Comment

ID: 40023797
Used VBA and chose the VeryHidden option for that page and it did what I needed.  No one else uses VBA so they shouldn't be able to unhide themselves.  I never new that existed.  Learn something new everyday.  

Expert Comment

ID: 40023851
You can also protect the "project" with a password keeping them out of the code as well.

From the tools menu in the editor,
go to VBAProject Properties, select the Protection tab, select "Lock project for viewing", then enter & confirm your password in the fields provided, and click OK.

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
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…

627 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