How do I keep Hidden Columns / Rows from being Unhidden / Referenc

[Guest]

1) I have Hidden Columns containing sensitive info in a Worksheet Protected
with a Password which Excel doesn't Print or Scroll into as one would expect
but can be unhidden without Unprotecting much less a Password if I copy to
another Worksheet or Workbook!?!. They even unhide automatically when I copy
to a Workbook in another Excel session. I know Excel isn't considered a
"secure" app, but come on, what's the point in offering a Password option
when anyone who can do a Copy and Paste can defeat it. I tried this in Excel
2000 and 2003. I've disabled Copy and Customize options in my Macros but
since Macros can be disabled on opening a Workbook, those are just added
speed bumps just like any security feature in Excel seems to be.

2) I noticed that values in Hidden Columns / Rows can be referenced from
other Worksheets or Workbooks. I've disabled other Workbooks from being
opened or being already opened but again that's just another speed bump since
they can open another session of Excel or disable Macros which leads to me my
next related question.

3) Is there a way to require Macros to be enabled for a Workbook to be
opened? I don't think this would have to sacrifice security since it could
be setup so that they would still be able to decline to open it if they don't
trust it.

Note that these Hidden Columns contain info that are restricted even to
authorized users of the Workbook so I can't just rely on the Workbook Open
Password which I heard is another speed bump as well since you can get a
utility to defeat it, but I can live with that.

Are these bugs that've been fixed? Are there workarounds? Thanks!

 

[Dave Peterson]

If you're putting sensitive data in an excel workbook and sharing it with
others, then you should expect that others will see it.

There is no protection that you can provide that will allow people to open the
file and not see that data.

Excel's security measures are not meant to provide this kind of
security--they're meant to keep the typical user from overwriting formulas.

 

[Guest]

Wow. That's uh .. pretty definitive. Were you a designer of Excel's
(in)security features or just one of their sleeper newgroup spokepersons?
Just kidding ... sort of.

Is your claim of what I "should" expect just based on the reality of Excel's
apparent lack of security features or actually on Excel's supposed original
design intentions?

I know that I personally and I would bet almost everyone who weren't told
explicitly otherwise "would" expect others will not be able to see or change
"anything" (not just Excel's data) that's accessed by a "password" unless of
course they have access to someone like an NSA cryptologist or world-class
hacker or their tools. Excel's own help says that "you can hide sheets that
contain 'sensitive' data" and "To 'prevent' others from displaying hidden
sheets, rows, or columns, you can protect the workbook or sheet with a
password." Nowhere in Excel's help, could I find any mention that data
hidden with a password could be accessed without one or that we should still
expect others will see it.

Well, in spite of what I "should" expect, I believe I've found a way to
prevent read and write access to parts of a Workbook that would require the
ability to override the Workbook Open or VBA Project passwords to foil which
I heard can be done with 3rd party utilities but my expectations have be
pounded so much at this point that I'd be more than happy if that's the only
exception.

1. Workbook WkbkA is optionally protected by an Open Workbook Password PswdA
which is provided to all authorized users.

2. Workbook WkbkA is empty except for a Workbook.Open Macro that: a) sets
Application.EnableCancelKey = xlDisabled and Application.Interactive =
False, b) calls Application.SendKeys "e" (to force Enable Macros), c) opens
WkbkB with a Password PswdB which is available only to the publisher and
maybe administrative users.

3. WkbkA's VBA Project is protected with a Password PswdC which is
available only to the publisher and maybe administrative users. PswdC can be
the same as PswdB to keep things simple.

4. WkbkB contains the actual sensitive data hidden in Columns / Rows /
Worksheets Protected by a Workbook Password PswdD and Worksheet Password(s)
PswdE1...(n) all of which can be the same as PswdB to keep things simple.

4. WkbkB's Macros are protected by a VBA Password PswdF which can be the
same as PswdB to keep things simple.

5. WkbkB's Macros use the Application .Open Events to only allow one other
Workbook to be already open in the same Excel Session (which would have to be
WkbkA) unless of course the user has access to PswdB (publisher / admin user)
and opened WkbkB manually or set up another Workbook to do so.

6. WkbkB's Macros use the Application .New and .Open Events to disallow
additonal Workbooks to be open in the same Excel Session.

7. WkbkB's Macros disable all occurences of the Copy Controls as well as the
Ctrl-C Key.

That's all there is to it! Easy, huh? Not! Heh, I dare anyone to find
another exception using just Excel's published features.

 

[Susan]

dave is very serious about his definitive statement.

try searching the newsgroup for "security" or "protect workbook" &
you'll find lots of discussions about what has been tried, and
apparently how easy it is for a determined hacker to break in.

granted, if you're dealing with the "average" user who wouldn't even
know where to BEGIN to look for code, you're probably safe. but dave
is warning you about relying on excel's "security" to protect
proprietary or confidential information.

you said "Hidden Columns contain info that are restricted even to
authorized users of the Workbook." if those authorized users WANT to
get in, they will be able to find out how.

& on a side note, if you're going to use the newsgroup to ask
questions & learn about stuff (which is why most of us are here),
isn't it kind of self-defeating to verbally dismiss & bash the person
who answered you? the "guru's" who answer most of the questions are
spending their personal time & efforts to help.
not kidding.
susan

 

[Dave Peterson]

I'm just a user who hangs around in the newsgroups.

The password to open a workbook is more difficult to break--but there is
software available that will do this for you--and it's not too expensive even
for the average person.

http://www.lostpassword.com

There are techniques that have been posted that will break your project's (VBA)
in a matter of seconds--they just remove the password--not try to guess it.

If you rely on a macro for protection, then macros can be disabled and your
protection is gone. I just change my security level to high (or medium and
answer a prompt)--or hold down the shift key to disable
auto_open/workbook_open. But allow the other code to run.

And after the project is unprotected, I can open (with macros disabled), change
your code and reopen so I can get by that protection.

As for workbook and worksheet (tools|Protection options), visit JE McGimpsey's
site:
http://mcgimpsey.com/excel/removepwords.html

====
And I like this one...

Change a setting first:
Tools|Options|Transitions Tab|Check Transition navigation keys

Start a new worksheet
unlock A1 (although this cell could be in a different workbook, too)
Put some "sensitive" data in columns B and hide those columns
Protect the worksheet
Type the = sign in A1 (to start a formula)
move the cursor to the right or down
Look at what you see!

You could try stopping the selection in that worksheet, but that unlocked cell
could be in a brand new workbook (opened before I open your workbook that stops
File|new)--or any old unlocked cell in your workbook.

======
So if you want to put sensitive data in an excel workbook, don't be surprised
if, er, when(!) it gets out.

 

[Guest]

I didn't get e.m. notifications of replies since my last post or I would've
replied sooner.

I just wanted to get to the point where someone would have to use
unpublished Excel features to view Hidden Columns and Rows. I forgot to
include a few items in my proposed solution and discovered a few more which
I've listed below. Regarding Dave's concerns, the Workbook with the hidden
Cells, WkbkB, requires an Open Password not known to the users, WkbkA won't
open WkbkB if its Macros are disabled, WkbkA's Macro will force Macros to be
enabled when opening WkbkB and both Workbooks disallow Formulas to be entered
and other Workbooks and Add-Ins to be opened or already opened (except for
ones opened via Windows Explorer which override the Application Object or
references Hidden Cells in their Workbook Open Event). I think I've gotten
to the point where one would have to write or paste in VBA code or use a
password breaking utility to view the Hidden Cells which is much better than
just doing a Save As to a different format, Copy and Paste or entering a
Formula that references the Hidden Cells. I'm not protecting SSN's here, so
this is plenty.

On a side note, Susan, I certainly did not that think these posters are
getting paid for their replies and do appreciate their replies. On the other
hand being a volunteer does not excuse one from being dismissive himself
which I felt that Dave was when he made what I felt was a curt statement
without addressing other attempts at a solution and without empathizing with
my expectation. In fact, he started his reply by simply stating what my
expectation should've been as if that should've been obvious which, like I
explained in my last post, most would agree is not. Anyway, I think I kept
my reply pretty light-hearted and professional compared to how I felt I was
treated and definitely compared to how many posters I've seen would've
replied had they felt the same. Regardless, I apologize if I mistinterpreted
and if I offended and I do appreciate yours and Dave's replies and anyone
else who may do so.

I forgot to mention the following in my previous post:

/1. All of WkbkA's Worksheets and Cells will be Locked and Protected with a
Password known only to the publisher and admin users. It can be the same as
the other pub / admin-only passwords.

/2. All of WkbkB's Worksheets and Cells will be Locked and protected with a
Password known only to the publisher and admin users. It can be the same as
the other pub / admin-only passwords.

/3. Hidden Columns and Rows in WkbkB will have the Hidden (formula) option
set in addition to the Lock option.

/4. WkbkB's Macros will disallow editing except via Macro options.

/5. WkbkB's Macros will disallow customization of existing CommandBar's.

I just realized the following was needed right after my previous post:

/1. WkbkA's Macros will not allow other Workbooks to be already opened in
the same Excel Session when WkbkA is opened.

/3. WkbkB's Macros will disallow creation of new CommandBar's and/or
addition of new Copy Controls.

/4. WkbkB's Macros will not allow a Formula to be entered when Cell changes
are allowed.

/5. WkbkA's Macros will not allow Add-In's to be installed or already
installed.

/6. WkbkB's Macros will not allow Add-In's to be installed or already
installed.

/ 7. WkbkB's Macros will disallow all Save As options.

/ 8. WkbkB's Macros will disallow all Save As Web Page options.

/ 9. WkbkB's Macros will disallow all Macro options.

/10. WkbkB's Macros will disallow all Macros options and the Alt-F8 Key.

/11. WkbkB's Macros will display an extra informative MsgBox to absorb the
"e" sent by WkbkA in case Security Level is Low so it won't affect other
Dialog Boxes shown when WkbkB's Opened like a Login.

12. If possible, replace # 11 with WkbkB's Macros will clear the Keyboard
buffer to absorb the "e" sent by WkbkA in case Security Level is Low so it
won't affect other Dialog Boxes shown when WkbkB's Opened like a Login.

/13. WkbkB's Macros will disallow all Open (Workbook) and Recent Files
options (vs. relying on the Application. WorkbookOpen Sub because it's not
called until after the callee's own Workbook. Open Sub.

 

[Dave Peterson]

I think it's a mistake if you think that your suggestions will protect any
confidential information from anyone who wants to get to it.

Excel just isn't made for this kind of thing--no matter how hard you try.

 

[Susan]

i hope you figure out what works for you - if you do, could you post
your solution so we can see how you did it?
these stupid newsgroups lend themselves to misinterpretation & easily-
taken offenses, despite the great info they dispense. i've done it
myself.
susan

 

[Don Guillett]

I didn't read all of this but as you have been told, many of us can get into
most files. If your question is how best to hide columns, use vba to make
"veryhidden" and then they must be unhidden with code.

 

[Norman Jones]

Hi Don,

'------------------
If your question is how best to hide columns, use vba to make
"veryhidden" and then they must be unhidden with code.
'------------------

Are you sure you are not referring to sheets rather than columns?

 

[Don Guillett]

Norman, Thanks.
As I said, I didn't read carefully. <G>