Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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?
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Locking certain cells in Excel 2007? | Excel Worksheet Functions | |||
Locking Excel cells | New Users to Excel | |||
Excel - locking cells | Charts and Charting in Excel | |||
Excel - randomly locking cells? | Excel Discussion (Misc queries) | |||
locking formula in cells in without locking whole sheet | Excel Discussion (Misc queries) |