Locking cells causing errors

[mauddib]

I have a workbook with 14 sheets. Across these sheets some different
things happen:

1) At least half the sheets have queries to an external database that
auto refresh when the contents of some cells change. (EG Cell A1
changes and the Query in Cell AA1 refreshes instantly.

2) When the query updates some cells have VLOOKUP so their contents
change too.

3) There is VB code which on a sheet being activated (eg you go to
sheet 2) then any rows from 1 - 100 that dont have data are hidden.

4) There is VB code that, when cell A1 changes, Cell A2 and A3 are
updated using VB. Cell A2 receives the username of the preson who
changed cell A1 and Cell A3 gets the time and date from now().

My problem:

Im trying to lock the sheets for user editing. That is, using the
example above, the user should not be able to edit the contents (and
preferably not see the formulas in) of any cell except A1 which updates
the whole sheet.

However when i lock the sheet using the Excel protect option then the
Queries throw up all kinds of errors. When I use VB to lock the sheet
it throws up other errors. Even when i set the sheet to
userinterfaceonly = true in VB.

Ive had a few solutions so far but they only solve one problem. For
example one solution lets the queries work but not the VB hide
coloumns. or vice versa.

nothing so far has let point 4 above work!

Where to go from here????

 

[Frank Isaacs]

VBA is not able to edit a protected cell. If you want point 4 to work,
you'll have to unlock those cells, or else unprotect the sheet, change the
cells, and re-protect it. Of course, that will compromise the security of
the sheet, because the password will be contained in the VBA code which is
part of the sheet.

This is sort of a Catch-22 situation - Excel really isn't designed for this
kind of thing, where you're trying to protect and track changes at the same
time. To ensure protection, the tracking can't work, because the cells can't
be altered. To ensure the tracking, the protection is compromised, because
the password is included in the file.

If you're not really concerned about how secure it is, you can put the code
in the VBA, but be aware that it is trivial to get it out.
--
HTH -

-Frank Isaacs
Dolphin Technology Corp.
http://vbapro.com