Solved

Protect an excel sheet

Posted on 2014-04-01
6
310 Views
Last Modified: 2014-04-23
Hi Experts,

I have an excel workbook with three sheet, I want to protect sheet2 to ( not allow anyone to copy the contents ).

Would you please advice how to do that.

What I want to do is after update the sheet2 of workbook test.xls put the protection. Anyone can open the workbook only the sheet2 can be view but no one can copy or choose the data of any cells of the sheet2 it will ask for password.


Thanks for your prompt response in advace.
0
Comment
Question by:alam747
6 Comments
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39971017
on the ribbon bar (review) select protect sheet, check the applicable boxes, and add a password.
0
 
LVL 10

Expert Comment

by:Scott Thomson
ID: 39971070
Be Careful of passwords in corporate environments. If you are away or you leave then it can be very hard to get the document back.

Or if you forget the password then you are stuffed. Or if you do want some people to see it but not others then some have the password and you can assure yourself that they give the password to others.

I would suggest using your companies security policy on a folder that contains that file. have them create 2 security groups one for read and 1 for read write. put you and the other trusted people in read write and all others as read.

this will only stop them editing the file not copying data from it.
0
 

Author Comment

by:alam747
ID: 39971227
In above way it still allow to choose cells and copy the contents which I do not want to.
It does not allow to change the data of the sheet2 but do not restrict to copy data from any cell, which I am looking for not allow other to choose to copy and paste it to elsewhere.


Please advice.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 10

Expert Comment

by:Scott Thomson
ID: 39971231
I would say the ultimate best way to do this is with a macro..

something similar to the one here

https://au.answers.yahoo.com/question/index?qid=20100103214412AAxtRD9

a macro is code within excel that governs the worksheet or document.
0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39971450
If you protect the worksheet and uncheck the "Select locked cells" option , then the user can't do a copy & paste. They can override this limitation by providing a password.

Note that the user can set a formula (in another worksheet or workbook) to return data from the protected sheet and get a copy of the data that way. You could, of course, use  macro code that:
1.  Won't let the workbook be viewed if VBA isn't enabled (that's one of the things that the code in Scott Thomson's Yahoo link does)
2.  Won't open the workbook if any other workbook is open
3.  Won't allow any other workbook to be opened if your workbook is open
4.  Won't allow the user to view any other worksheets in the workbook
5.  Won't allow the user to insert new worksheets in the workbook
6.  In Excel 2003 and earlier, you can use VBA code to remove the Copy button from the toolbar and shortcut key combination. In Excel 2007 and later, you can remove the Copy button and shortcut key combination by editing the XML in the file (very fiddly, but can be done)

But even if you did all that, there is a widely available macro that can crack the worksheet protection password if it was installed in Excel 2010 or earlier. Once the user runs that macro, he can unprotect the worksheet and copy data readily. Excel 2013 has more sophisticated worksheet protection, but it can still be defeated easily by someone that knows how.

The user can also copy data from a hidden worksheet (one of the things that the code in Scott Thomson's Yahoo link does) into another workbook using formulas if macros are not enabled in your workbook. The user could also use a one-line macro to copy your data from the hidden worksheet into his own workbook.

Bottom line: it is impossible to absolutely, positively prevent the user from copying your data. The best you can do is to make it difficult enough that honest people remain honest. A scoundrel will inevitably be able to defeat your protections.
0
 

Author Closing Comment

by:alam747
ID: 40018880
Thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Set value in a excel worksheet to equal the length of the previous column in C# 10 73
Mac-based software for Excel 8 39
Consumer laptop recommendation 6 67
Compile Error 7 41
The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

867 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

19 Experts available now in Live!

Get 1:1 Help Now