Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
prevent user from selecting and changing a sheet
I have written a database front end with Excel that uses some built in Excel
functions. One thing I use is a sheet to store intermediate variables, user settings and so on. This sheet needs to be updated programatically, but I don't want the user to accidentally change anything. I could go through the code and protect and hide then unprotect and unhide the sheet as the programs need to interact with the sheet but this seems messy and I don't want the user to see the sheets changing. Is there a way to keep the sheet open and available to the programs without allowing the user to select the sheet or change its contents? Any thoughts would be appreciated. TIA Dan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
prevent user from selecting and changing a sheet
Your program may be able to do everything it needs to do without the sheet being
visible. If your code selects ranges, work on that range directly: instead of: with worksheets("Hidden") .visible = true .unprotect password:="Hi" .range("a1").select activecell.value = "it changed" .protect password:="Hi" .visible = xlsheethidden end with You could keep it hidden (and maybe unprotected???): with worksheets("Hidden") .range("a1").value = "it changed" end with === You could also protect the worksheet in code that allows the worksheet to be changed by code--but not by the user: Option Explicit Sub auto_open() With Worksheets("Hidden") .Protect Password:="Hi", userinterfaceonly:=True End With End Sub It needs to be reset each time you open the workbook. (excel doesn't remember it after closing the workbook.) dirt wrote: I have written a database front end with Excel that uses some built in Excel functions. One thing I use is a sheet to store intermediate variables, user settings and so on. This sheet needs to be updated programatically, but I don't want the user to accidentally change anything. I could go through the code and protect and hide then unprotect and unhide the sheet as the programs need to interact with the sheet but this seems messy and I don't want the user to see the sheets changing. Is there a way to keep the sheet open and available to the programs without allowing the user to select the sheet or change its contents? Any thoughts would be appreciated. TIA Dan -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Prevent user changing pivot chart layout | Excel Worksheet Functions | |||
Prevent User to unlock Sheet Tabs | Excel Worksheet Functions | |||
Prevent selecting locked cells | Excel Programming | |||
prevent user to rename sheet | Excel Programming | |||
How to Prevent User from Unhiding Sheet | Excel Programming |