Modify data on a hidden sheet?

[Robert Crandal]

Some of my sheets have been marked as "xlVeryHidden".
These sheets contain data that I do not want users to be
able to modify.

If a user knows the codename or tab name of any of
these hidden sheets, could they actually be able to
modify data on that sheet in any way?? Can they enter
a formula onto a visible sheet that references a hidden
sheet to change the data??

thank you!

 

[Gord Dibben]

Formulas on a visible sheet cannot alter anything on another sheet or
anywhere for that matter.

Formulas return values to the cell in which they are written.

Back to first question...............

Yes, through VBA a user could alter values on a hidden sheet if they knew
the tab name or codename of that sheet.

Or they could run VBA to unhide the sheet and change everything on it.

Or even delete it.


Gord Dibben MS Excel MVP

 

[Robert Crandal]

What if my VBA project is password protected so they cannot
access any of the code modules??? Will that help??

What are a few ways I can prevent anyone from modifying
data on a hidden sheet??? I need to cover my bases as much
as possible.

thank u

 

[Gord Dibben]

See in-line

What if my VBA project is password protected so they cannot
access any of the code modules??? Will that help??

Not much. I'll just run code from another workbook to access your hidden
sheets if I know the names.

What are a few ways I can prevent anyone from modifying
data on a hidden sheet??? I need to cover my bases as much
as possible.

Since you're dealing with Excel's relatively weak internal security you
would be hard-pressed to cover all bases.

How sophisticated are your snoopy users and how much time and effort are
they willing to expend?

If the hidden worksheets were protected.....all cells locked and unprotect
password set, users would have to know password to alter cells even if they
could unhide the sheets.

But easy enough to crack sheet passwords.

If the workbook structure was protected with a password, hidden sheets could
not be unhidden through code.

But easy enough to crack workbook protection passwords.

I am assuming the hidden sheets' cells are referenced by the visible sheets
so simply removing those sheets is not an option.


Gord

 

[Dave Peterson]

Lock all the cells on the worksheet and protect the worksheet. But even that
won't stop people who are really interested.

The hidden-ness of the worksheet doesn't really offer any more
protection--except that it's out of sight, so it may be out of mind.

 

[Chip Pearson]

Locking the VBProject won't help. The user could simply run

Sub AAA()
Dim WS As Worksheet
For Each WS In Workbooks("YourWorkbook.xls").Worksheets
Debug.Print WS.Name
Next WS
End Sub

to list all the sheets, regardless of whether they are visible,
hidden, or very hidden. You can protect the structure of the
workbook, but there are plenty of ways to get around that.

The bottom line is that Excel security sucks. It is good enough to
prevent the honest user from making an honest mistake, but it isn't
good enough for anything more than that.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]