Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protecting Formulas
Hi group,
I'm a relative Excel newbie and have received excellent help in these groups previously. This time I'm looking to protect the work that I've spent many hours on: I have several workbooks which are used by warehouse personnel to print inventory sheets and associated barcode labels. For various reasons we have decided to use Excel to manage and print these rather than Access or a VB program. The problem arises when the warehouse employees enter data incorrectly in the files. Basically, there is only one cell per worksheet (B2) that should ever have data entered into it. From the data entered in B2 (as well as static data in other parts of the workbook/sheet) a query is made to our SQL server and data is returned. The sheet is then printed, and data saved to a text file. The formulas range from simple =A2&B2 to more complicated SQL.REQUEST statements. I don't want people typing in over these cells. Now I've used Tools-Protect Sheet and Tools- Protect Workbook with success, but the problem is that protecting a cell causes its associated query to not update. It seems that protecting a workbook or worksheet also keeps the query or SQL.REQUEST from updating. I can select individual cells to protect, but even protecting a cell which contains a COUNTIF stops it from updating the quantity. Should I somehow use the Workbook_Open() event to reapply the formulas? It doesn't seem very efficient, and there are possibly a dozen formulas per worksheet, and from 4-20 sheets per workbook. I thought of denying write access to the network share, but the (legitimate) changes need to be saved when the document is closed. My favorite idea so far is a popup that occurs anytime someone types in a cell other than B2, which makes rude comments about their lineage and reverts back to the original formula :) Any suggestion is appreciated. -- Mike Brown Asset Forwarding Corp. EPA-compliant Recycling DoD 5220.22-M Data Elimination http://www.assetforwarding.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
protecting formulas without protecting sheet so grouping still wor | Excel Discussion (Misc queries) | |||
Protecting Formulas | Excel Discussion (Misc queries) | |||
PROTECTING FORMULAS | Excel Discussion (Misc queries) | |||
Protecting Formulas | Excel Discussion (Misc queries) | |||
Protecting Formulas | Excel Discussion (Misc queries) |