M
Mike Brown
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
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