Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Locking Cells in an Automated SpreadSheet
Hi,
I am creating a spread sheet which will be used to lay out bills 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 Ive created a number of drop-down menus which the user must fill in, when they are all selected the 50% of the BOM which is generic autopopulates with partnumbers, qtys etc. Its important that the user doesnt 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 cant 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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Locking Cells in an Automated SpreadSheet
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. "Mick Henn" wrote: Hi, I am creating a spread sheet which will be used to lay out bills 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 Ive created a number of drop-down menus which the user must fill in, when they are all selected the 50% of the BOM which is generic autopopulates with partnumbers, qtys etc. Its important that the user doesnt 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 cant 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Locking individual cells in an excel spreadsheet | Excel Worksheet Functions | |||
Help with automated size in cells and two lines | Excel Discussion (Misc queries) | |||
Locking cells in a spreadsheet | Excel Discussion (Misc queries) | |||
locking formula in cells in without locking whole sheet | Excel Discussion (Misc queries) | |||
need help with locking spreadsheet | Excel Discussion (Misc queries) |