Locking Cells in an Automated SpreadSheet

[Mick Henn]

Hi,

I am creating a spread sheet which will be used to lay out bill’s of
materials (BOMs). The BOM is a list of all the manufacturing components which
are needed to created a finished product. Roughly 50% of the components are
generic and constant so I’ve created a number of drop-down menu’s which the
user must fill in, when they are all selected the 50% of the BOM which is
generic autopopulates with partnumbers, qtys etc.

It’s important that the user doesn’t overwrite the formulae in these cells
but at the moment there is nothing preventing that from happening so what I
need to know is, is there a way of locking these cells so that they can’t be
overwritten without a password but they can be formatted, deleted etc.?

Also is there a way of locking the choices in the drop down lists so that
once the user has made their selection there is a warning which appears if
they try to change the selection? It would be preferable if I could set up
the spreadsheet so that the user has minimal steps to remember i.e. format
cells – protection tab – etc.

Many thanks,
Mick

 

[JLatham]

Mick, in Excel 2003 you can choose to protect a sheet with options available
to permit formatting even the locked cells. (Some of the other options
available don't seem to work - such as Sorting, deleting/inserting
rows/columns - or at least they never have for me).

By default all cells on a worksheet are protected (locked), but that lock
doesn't take effect until you use Tools | Protection | Protect Sheet to
activate it.

What you can do is go to the cells where you want to permit your users to
enter information and use Format | Cells and clear the [Locked] checkbox for
those cells. Then when you protect the sheet, the users will be permitted to
enter/delete entries in those cells, but not in the ones that are still
locked and that hold your formulas and such.

If you add any VBA code to the workbook that changes the contents of a cell
or attempts copy/paste from cells, those cells will need to be unlocked also.

Hope this helps some.