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

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