Solved

How Do I password protect individual Worksheets in a workbook

Posted on 2014-07-27
29
328 Views
Last Modified: 2014-09-20
I have a workbook that has multiple worksheets that are created and maintained by different people.  I want the worksheets password protected so that only certain people can access them.  Also I need for one person to access all worksheets.

I've found how to password protect the entire workbook and how to protect individual cells in worksheets but not how to password protect individual worksheets

How do I do that for Excel 2013.  Also these machines are in a Workgroup not a Domain, if that makes any difference

Thanks
0
Comment
Question by:c7c4c7
  • 11
  • 7
  • 5
  • +1
29 Comments
 
LVL 12

Expert Comment

by:James Elliott
Comment Utility
Does protect/unprotect worksheet not work for you, with all cells marked as 'protected'? Each worksheet can carry its own password...
0
 

Author Comment

by:c7c4c7
Comment Utility
I don't want the worksheet to even come into focus if they do not know the password.  Once they are in the worksheet they have to be able to do anything they want.
0
 
LVL 12

Expert Comment

by:James Elliott
Comment Utility
This is not easy to achieve. You could use smoke and mirrors to achieve the 'effect', probably by hiding the sheet tabs and allowing navigation through a main menu sheet with command buttons. But it could quite easily be circumvented.
0
 
LVL 4

Expert Comment

by:yuppydu
Comment Utility
Go in VBA, click on the sheet you want to hide and then in the property window under Visible you can set it as xlSheetHidden or xlSheetVeryHidden.
Diiference between the two is that xlSheetHidden can be unhidden from Excel GUI, while xlSheetVeryHidden has to be unhidden from VBA editor.
Both status do no interfere with VBA, in the sense that you can work on them through VBA.
Using VBA you can then easily let the user hide/unhide using a dialog box which asks for a password
0
 
LVL 12

Expert Comment

by:James Elliott
Comment Utility
Yuppydu is right, but you need to be careful that the user doesn't save the workbook in a state where the sheets are visible.

A Before_Save routine that hides everything again would be necessary.
0
 
LVL 4

Expert Comment

by:yuppydu
Comment Utility
I would recommend having a public function which is run everytime the workbook is closed and resets the sheets as they suppose to be. Or you could have a routine when the workbook is opened which set all the defaults. You can hide sheets, columns, rows, and set just about anything you need. On workbook open you could ask for the password/s. Setting different password will select the user profile and hide/unhide whatever you want
0
 
LVL 12

Expert Comment

by:James Elliott
Comment Utility
You then have to somehow ensure that the users selects 'enable macros' otherwise all of the sheets will be exposed.

Again, this is possible, but just a nod to the law of unintended consequences!
0
 
LVL 4

Expert Comment

by:yuppydu
Comment Utility
Excuse me Jell, what are you trying to provide? The author has not commented yet and I do not think this should be a discussion between the two of us. Moreover your last comment is out of line: if I save the workbook with all the hidden sheets saved as xlSheetsVeryHidden nothing will be exposed. if you start manipulating a workbook you should know strenghts and weaknesses and there wont be unintended consequences!
0
 
LVL 12

Expert Comment

by:James Elliott
Comment Utility
I'm trying to help, not start an argument. I don't see any of my comments being out of line.

I've tried exactly this myself and there are things to consider which weren't detailed in your posts.

nothing personal and sorry if i offended you.
0
 

Author Comment

by:c7c4c7
Comment Utility
It sounds like I cannot have worksheet password access to the worksheets.

I don't have any objection to using code to achieve the end result but there are several problems.
1). When sheets are added code would have to be modified.
2). There's no way of protecting the code from prying eyes
3). Passwords would be changed so the code would have to be modified
4). If there was an external file that had a relationship between passwords and sheets that would be expose is someone looked at the code
5). I don't see a problem with the Enable Macros I would just add the directory to the Trust Relationship

Seems like a big hole in Excel if you can only password protect the entire workbook and not the individual sheets as well.  I would think this would be a common request
0
 
LVL 4

Expert Comment

by:yuppydu
Comment Utility
1) When you add new sheets they can be automatically set to VeryHidden upon opening of the workbook
2-4) That is the weakest point. VBA password protection is very weak.
3) Passwords, to the best of my knowledge have to be hardcoded.

To summarize it can be done but if you want high coding security you need a proper compiler and the use of different programming language. Even using .NET security is very low and you need an obfuscator to make it stronger. We are talking about high level programming where security is low by definition.

Good luck with your project. If you decide to give it a try with VBA I'll pick it up!!
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
YuppyDu - Re your comment that passwords have to be hardcoded, thats not strictly true.

I have a number of files where the password is written in a cell and then the cell is referred to via a Named Range, the Named Range uses an offset function so that the cell is not directly referred to, thus making a couple of levels of "security" but this would only stop accidental interference and wouldn't stop the determined "hacker". The VBA to unprotect the sheet then refers to the named range. The cell containing the written word could be on the hidden sheet so only the person that knows the password to unhide the sheet would then be able to see the password and if it becomes known the user that has visibility of that sheet could change it and the code would not need changing.

By imposing a Password at the workbook level as well, unauthorised users would not be able to link to the file to extract the data from hidden sheets but all authorised users would need to know the workbook password so would be able to get at other users' sheets within the workbook using linked formulas.

Alternatively you could use a central file to open other workbooks which are password protected rather than worksheets.

Thanks
Rob H
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
Is your issue regarding visibility of other users' data or modification thereof?

If it just modification of data, there is an option whereby you can allow certain users to edit certain ranges.

On the Review Tab of the Ribbon there is the option "Allow Users to Edit Ranges". Click this and you will see a pop-up to follow through. I haven't used this feature so don't know the full use of it.

Expanding on the comment earlier regarding the password being hidden within the worksheet and referred to by named range. The actual position of that password could be variable as well. See attached and look at named range PASSWORD (bit obvious to call it that in reality). Press F9 and the position of the word will move within the yellow area but the named range will still pull it as seen in cell J23, this cell is only shown for illustrative purposes so would be deleted.

Thanks
Rob H
Dynamic-password-position.xlsx
0
 
LVL 4

Expert Comment

by:yuppydu
Comment Utility
To make things a bit more secure you can create a named range which does not refer to any cell but has the password written in the "refers to" box, then in the VBA you can hide the range from the range manager by using

Names("password").Visible = False

By doing this the only place where your password is recorded is in the range manager but the range is not in the list.

Again you can try to make things as confusing as possible but, at the end, the weak point is that your VBA code is not safe at all.  Anybody with a little bit of knowledge can read your code.
0
 

Author Comment

by:c7c4c7
Comment Utility
Rob Henson Comment"
By imposing a Password at the workbook level as well, unauthorised users would not be able to link to the file to extract the data from hidden sheet

Answer:
This would kill the whole idea of having everything in a single workbook, not good

Rob Henson Comment:
Is your issue regarding visibility of other users' data or modification thereof?
Answer:
It's the visibility of data, the workbook/sheets contain amounts of sales commisions, that should not be shared anymore than necessary.

yuppydu Comment:
To make things a bit more secure you can create a named range which does not refer to any cell but has the password written in the "refers to" box, then in the VBA you can hide the range from the range manager by using

Answer:
That's the 1st time I've heard of a thing like that I'm not familiar with how that works.
How do you create a named range without referring to any cells
You create a named range and then what

This is all good stuff but I'm a firm believer in the KISS method,  and with these folks I've learned that if they can screw it up they will

Are there no third party vendors that provide sheet security and have all of the infrastructure in place?
0
 
LVL 4

Expert Comment

by:yuppydu
Comment Utility
I can recommend a software for spreadsheet protection. It is very well made and very strong security, although it does not exactly what you are looking for (i.e. protecting/hiding sheets depending on the user) but it does secure your VBA project and your hidden sheets/formulas from any kind of attack.
If you are interested let me know
0
 

Author Comment

by:c7c4c7
Comment Utility
Yuppudu

Sure let me know what it is, I will see if it is something I can work with
0
 
LVL 4

Expert Comment

by:yuppydu
Comment Utility
Write a PM and I'll give you all info
0
 

Author Comment

by:c7c4c7
Comment Utility
Thanks for the help
0
 
LVL 4

Expert Comment

by:yuppydu
Comment Utility
I understand the point you are making, not to worry. I have switched to PM because I had to suggest a commercial software

Yuppydo
0
 

Author Comment

by:c7c4c7
Comment Utility
Sorry, you deserve the points
0
 
LVL 4

Expert Comment

by:yuppydu
Comment Utility
It's ok. Hope you have found the protection software helpful.
0
 
LVL 4

Expert Comment

by:yuppydu
Comment Utility
Dear Netminder,
I am aware of that policy but, nevertheless, I believe that if the third party software is not open source it should be "promoted" via PM. Although it is not my software no one knows if I get a cut in promoting it. I am an ex banker, founder of a software house. I am en ex banker because my ethic did not allow me to continue on that career... like to keep it that way and be on the safe side.

Let's not make a big issue out of this. It's really not an issue with me getting or not getting the points.

Regards to you and the community
Yuppydo
0
 
LVL 4

Accepted Solution

by:
yuppydu earned 250 total points
Comment Utility
Fair enough.
When I first started my company I only had a massive (over 20.000 lines of VBA) spreadsheet for which I needed strong protection.
After trying many different software for this task I have found LockXLS (www.lockxls.com) to be by far the best around, both in term of strong protection, ease of use and pricing.
I strongly recommend it. I have tried to crack it with various software and never succeed it.

All the best
Yuppydo
0
 

Author Comment

by:c7c4c7
Comment Utility
Now that yuppudu has posted the solution and EE says there is not a problem with that are you going to automatically award him the points, as EE took them away?  Or do I need to award them again?

Thanks
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Excel - Scroll Speed 3 24
Excel  Worksheet Relationships 7 22
Excel Macro - Lookup-Highlight 4 21
TT Column Arrange 10 25
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
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…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now