Lock workbook

  • Thread starter Thread starter QB
  • Start date Start date
Q

QB

I have a rather straight forward workbook with a single worksheet. It has
several columns of data which do some simple math....

I now have to distribute the workbook, but when I do so I do not want the
users to be able to modify the formula, not even be able to visualize the
formulas behind the values. That said I still need them to be able to add
in new data (new row) and drag the formula (that they can't see) to the new
row.

Is this possible?

Thank you,

QB
 
Probably the best way to deal with this would be to put a button (or text
box)_ attached to a macro that would open up a UserForm for them to enter the
information that is to be placed into a new row.

The user form would then have a button to "add to spreadsheet" which could
also provide pre-posting validation of the data they entered. At the end of
that process it would unprotect the worksheet, add the new data in a new row
and pop in the formulas needed, and finally, put the worksheet back into
protected state.

You'd need to set all cells on the sheet to "Locked" and "Hidden" (Format,
cells, [Protection] tab). Assign a password to the sheet and you can even
allow them to select locked and/or unlocked cells.

To prevent them from opening the VB Editor and seeing the password to the
sheet in it, you'll need to password protect the VBA project also: in the VB
Editor -- Tools, VBAProject Properties and use the [Protection] tab. It's a
good idea to actually write this password down, and also to keep a copy of
the file for yourself that does not have the VBA project password protected.

Two things to remember: password protection in Excel workbooks/worksheets is
very weak and there are any number of free or for-$ tools available that will
unprotect a sheet or workbook in minutes, if not in seconds. The key here is
that in workbooks and worksheets, it is "PROTECTION" from accidental
alteration rather than "SECURITY" to prevent unauthorized access.
 
Back
Top