prevent user from selecting and changing a sheet

[dirt]

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]

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.)