Solved

Protecting a workbook from being edited in XL 2003

Posted on 2011-09-20
7
243 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

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…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
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.

757 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

18 Experts available now in Live!

Get 1:1 Help Now