[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 282
  • Last Modified:

Protecting a workbook from being edited in XL 2003

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
contrain
Asked:
contrain
  • 3
  • 3
3 Solutions
 
Patrick MatthewsCommented:
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
 
contrainAuthor Commented:
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
 
sanofi-aventisCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Patrick MatthewsCommented:
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
 
contrainAuthor Commented:
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
 
contrainAuthor Commented:
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
 
Patrick MatthewsCommented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now