?
Solved

Protecting a workbook from being edited in XL 2003

Posted on 2011-09-20
7
Medium Priority
?
281 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 93

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 800 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 1200 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 93

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

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…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

649 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