Protecting a workbook from being edited in XL 2003

Posted on 2011-09-20
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.
Question by:contrain
  • 3
  • 3
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

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

Author Comment

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.

Assisted Solution

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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 300 total points
ID: 36583387

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 :)


Accepted Solution

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.

Author Closing Comment

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.
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36716421

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.


Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

786 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