Solved

Protecting a workbook from being edited in XL 2003

Posted on 2011-09-20
7
265 Views
Last Modified: 2012-06-27
I have multiple worksheets in a workbook in XL 2003, I want to send to someone to allow them to look at the data, but not be able to alter the data. So I don't necessarily want them to need a password just to open, but I also don't want them to be able to alter the data and save under another name, just look at the data, sort of a read-only without being able to edit at all.
0
Comment
Question by:contrain
  • 3
  • 3
7 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36570634
You can protect the worksheets with a password, but that can be hacked by any motivated user within seconds.

Why not simply send the data as a PDF file?  That makes it viewable, but not editable.

I use PDF995 to create PDFs, available from http://www.software995.com/

It's free if you don't mind looking at ads.
0
 

Author Comment

by:contrain
ID: 36570813
Yes, I have a program that will convert XL to PDF, and it does prevent editing, but I'd like to keep it in an excel format for cell referencing, etc. Thanks for the good suggestion.
0
 
LVL 2

Assisted Solution

by:sanofi-aventis
sanofi-aventis earned 200 total points
ID: 36582789
You can place code in the workbooks BeforeSave event to cancel the update. You can passward protect the VBA project which will not require a password to open it the workbook.

Private Sub Workbook_BeforeSave( ByVal SaveAsUI as Boolean, Cancel as Boolean)
Cancel = True
End Sub
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 300 total points
ID: 36583387
sanofi-aventis,

Yes, and all the user has to do to circumvent that is disable macros.  Or use one of the dozens of free to cheap VBA password hackers available online.

It also won't prevent the user from simply copying and pasting the data to another file (unless worksheet protection is used, with selecting of locked cells disabled).  And even then, the passwords for worksheet protection are even weaker than the passwords for the VBA project, and can be hacked with very simple VBA code.

And none of those will prevent the user from harvesting the values using formulas.

Once you give an Excel file to someone, you've already lost control of it :)

Patrick
0
 

Accepted Solution

by:
contrain earned 0 total points
ID: 36713380
Actually I did find a workaround (Emphasis on the word "around") that seems to work, but it doesn't involve saving the workbook wholesale but each sheet individually: I highlight all of the first sheet, Go to the Format menu, Cells, Protection tab and make sure the check mark is in the Locked check box. Click OK. Then I will go to the Tools menu, Protection, Protect Sheet, make sure the check mark is in the Protect Worksheet and contents of locked cells box, and uncheck what I do not want anyone else to be able to edit. I then give it a password, and click OK.

It worked, but I have to do this for each sheet in the workbook. Not a big issue with just 5 sheets as my workbook had, but you can see the resulting problem should a workbook contain many sheets.
0
 

Author Closing Comment

by:contrain
ID: 36898992
Even though I found a work around, I want to award the Experts points based upon their  help with this. sanofi-aventis provided a VBA code (which is not something I do often, but who said all answers were easy or easily accessable) that would cancel any updates which serves the purpose of anyone trying to manipulate the doc, I was just hoping for a way to thwart them from trying in the first place. matthewspatrick gave great advice about how even VBA methods can be circumvented, (I wasn't aware there were VBA password hackers available on the 'net) and also mentioned other ways this type of protection can be breached. So his "ounce of prevention" was very valuable to prevent me from making the mistake in the first place. His last line about giving an Excel file to someone really brought it all home to me in a real way, and it's good to know that's the bottom line, no matter what you do to protect yourself and your data.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36716421
contrain,

As my comments above make plain, do not be fooled into thinking that your data are now "protected".  Motivated users will still have several means at their disposal of copying the data, regardless of whether the worksheet is protected.

Patrick
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
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!
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.
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…

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