Locking cells in Excel
 

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????


---
Message posted from http://www.ExcelForum.com/

Locking cells in Excel
 

If you have a method that gets your VB running fine, and accomplishes the task you want, but still having problems with then setting up the queries in VB code, and having the results pasted into the sheet would fix that problem. Another option is to have code that will unprotect the necessary sheet(s) prior to the queries being run. Not sure how to disable queries by VB code, but if you can disable the query until you enable it to be run by the VB code, then that would also solve your problem. Maybe something like, disabling auto recalculate on the worksheet, with all the cells protected except A1, when A1 gets changed, it fires off VB code, that first unprotects the sheet, then forces a recalculation, and begins running the other VB code you have. Prior to "finishing" the VB code then re-protects the sheet(s). How's that sound?

Locking cells in Excel
 

use the workbook level SelectionChange event. If the user selects anything
but A1, have the code select A1. This will restrict the user to using the
scrollbars to see the rest of the spreadsheet and not being able to select
elsewhere.

Of course if the user disables macros, then this won't work, but neither
will any of your other macros.

You also might consider redesigning your worksheet so things you don't want
the user to see are on hidden sheets and only things the user should see are
on a single report worksheet or multiple report worksheets as appropriate.

Or, you could force all interaction to be done through a userform.

--
Regards,
Tom Ogilvy

"mauddib" wrote in message
...
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????


---
Message posted from http://www.ExcelForum.com/