VBA to prevent other users from making any changes - a true Read Only

[Jim885]

I have an Excel file with many worksheets which already have certain cells locked and password protected to prevent users from making changes to the worksheets. So changing all cells in a worksheet to LOCKED upon opening the file will not work, since each sheet has various locked cells.

We want all employees to be able to open this file and be able to look at each sheet. However, we don't want them to be able to make entries into cells (which "read only" will still allow).

Is there any way to make a read-only file a true read-only?

 

[Fazza]

how about making a pdf copy? ALT-F-H-E-F

 

[Jim885]

Fazza,
Thanks for the idea, but the file will change daily, and keeping daily pdf's on a server will cause confusion. The users who will "see-only" will have shortcuts on their desktops that will lead them to the original file.

 

[Fazza]

OK, Jim. What about applying worksheet protection to every sheet, and workbook protection? Does that do what you need?

And exactly what you're trying to protect isn't clear - even with workbook & worksheet protection information won't be very secure,

Regarding the objections to the pdf - change the pdf file daily, always the same name. (Move the original file somewhere else.) Be a one off change of desktop shortcuts.

 

[Jim885]

Thanks again Fazza.
We don't have to worry about the security of Excel because all the people using the file have very basic knowledge of Excel. Most of them only know how to create a basic sum formula, and that's the highest level.

We just don't want certain (reckless) people to be keying into cells that a "Read-Only" file allows, and then saving the file as a copy. Then someone else thinking the copied file is the real file with all the correct information.

We have very simple-minded people at this company and need to make this file dummy-proof and accessible to the dummies without one of them causing chaos.

Unfortunately for this company, many "computer dummies" have a major influence, and if they can't use a file or a program the whole concept dies. This has happened three time already.

Protecting the workbook won't work, because many people who need to look at the worksheets can also end up "playing around" and saving the file with changes that they shouldn't have made.

The 20+ worksheets are already password protected with some columns unlocked so that the people that need to make changes to the sheets can do that (and save the file) without the worry of accidently tabbing into cells with formulas and changing them.

The problem is that others will look at the same sheets and possibly make entries. Then when they close the file, they will be asked if they want to save the file. Then we end up with multiple copies and the computer-dummies complain and then the whole system falls apart again.

After writing all this, I think it's a "no-win" situation ... with this company.

 

[Fazza]

as a general comment, it can take a lot of work to make a file dummy proof and put limits on users, you can tie yourself in knots

hence, consider a different approach. only guessing if this would be suitable, but may give you some ideas. have no-one except the "keeper of the file" use the master file & everyday email a copy to everyone who should see it. the recipients can play with their emailed copy & change whatever they want. if there is to be the possibility of acceptable edits, have the desired edits recorded somehow within the file in a manner that makes them easy when that copy of the file is emailed back to the keeper of the file for the particular edits to be made to the master file. some smart VBA might make this work neatly

 

[Jim885]

Thanks for the ideas. I think your idea of emailing a pdf of the spreadsheets to the respective people will be the best method.
Saves me a lot of work and aggravation.

 

[Fazza]

great. all the best, Jim

be sure to check the page setups are correct for all worksheets - things like landscape/portrait, fit to page, footers + headers (with file name, worksheet name, dates, page numbers, etc)

another idea is to have the very first worksheet with notes, revision/issue date/time (formula for this so every time it prints it auto updates), contact info, instructions, answers to FAQ, , maybe revision history, comments on the source/currency/history/provenance of the data, etc, etc

 

[Lexcon07]

Hi Jim,

Just a couple of quick questions to clarify.

• You want an "Administrator" to be able to edit the workbook, but only want others (i.e. "Users") to be able to view, but not make any changes, correct?
• Why do you have some cells locked and unlocked at this time? Is that to prevent the "Administrator" from making changes or to prevent the "Users" from making changes in previous iterations of the workbook?
• If you use the "Protect Sheet" are you able to get the level of security you want on a sheet by sheet basis? The following are the activities you can allow or disallow users to perform when the worksheet is "Protected".
  • Select locked cells
  • Select unlocked cells
  • Format cells
  • Format columns
  • Format rows
  • Insert columns
  • Insert rows
  • Insert hyperlinks
  • Delete columns
  • Delete rows
  • Sort
  • Use Auto Filter
  • Use PivotTable & Pivot Chart
  • Edit Objects
  • Edit Scenarios

I have a solution that would allow you to click a single button and use a password to lock and unlock all worksheets at the same time, while assigning certain privileges to the Users while the model is locked. If you don't want them to be able to edit anything, that can be done too.

If you you think this might help, I would be happy to provide you with the details. It's not super complicated, but does require a UserForm in VBA.

 

[Jim885]

Hi Lexcon07,
Thanks for all your thoughts, advice and offer to help.
To answer your questions ...
1. Yes
2. Some cells are locked to allow senior management to tab through the spreadsheets without entering cells which contain formulas, and make changes to the unprotected cells.
Then we want users (managers) to only view the spreadsheets without being able to tamper with the data.
3. Hence, each sheet is password protected and only allows the senior management to only be able to enter the data into the unlocked cells.
4. The problem is further enhanced by the new CEO who wants to keep the file "super-simple" due to his lack of knowledge of Excel. Therefore, he would not want anything that would be complicated. I have already thought of a UserForm for data entry. But the CEO had already killed a month-long project I developed because he and the managers were intimidated by having to "learn" something new.

It seems the company is challenged by the people who run it. This is a multi-million dollar company, yet they run it like they are operating out of their garage, They don't even have an inventory or purchasing system.