Solved

Protect Hidden Sheet in Excel

Posted on 2014-04-25
3
390 Views
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.
Thanks
0
Comment
Question by:nursecore
  • 2
3 Comments
 
LVL 7

Accepted Solution

by:
Steve earned 500 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

0
 

Author Closing Comment

by:nursecore
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.  
Thanks
0
 
LVL 7

Expert Comment

by:Steve
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.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
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!
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

839 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