Don't unhide my worksheets

Dean

unread,

Oct 19, 2006, 9:40:11 PM10/19/06

to

I wrote a macro that hides worksheets under certain conditions. Is there a way so that users can't simply unhide those sheets, perhaps via some sort of password they wouldn't know? Does the workBOOK need to be protected to accomplish this? If so, what are the other adverse implications of protecting a workbook? I don't want a cure worse than the disease.

Thanks!

Dean

Dave Peterson

unread,

Oct 19, 2006, 9:46:58 PM10/19/06

to

You can protect the workbook:

Tools|Protection|Protect workbook|check the Structure option.

Give it a nice memorable password.

Be aware that this password is easily broken--so it won't stop everyone.

And this will stop the users from renaming, adding, deleting, moving worksheets.

ps. This is a text only newsgroup. Please post in plain text--not HTML.

--

Dave Peterson

runde...@gi.konicaminolta.us

unread,

Oct 19, 2006, 10:47:24 PM10/19/06

to

In your active sheet,
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Sheet2.Visible = xlSheetVisible Then
Sheet2.Visible = xlSheetHidden
End If
End Sub

> ------=_NextPart_000_0011_01C6F3AE.02124A50
> Content-Type: text/html; charset=iso-8859-1
> Content-Transfer-Encoding: quoted-printable
> X-Google-AttachSize: 892
>
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> <HTML><HEAD>
> <META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
> <META content="MSHTML 6.00.2900.2963" name=GENERATOR>
> <STYLE></STYLE>
> </HEAD>
> <BODY bgColor=#ffffff>
> <DIV><FONT face=Arial size=2>I wrote a macro that hides worksheets under certain
> conditions.  Is there a way so that users can't simply unhide those sheets,
> perhaps via some sort of password they wouldn't know?  Does the workBOOK
> need to be protected to accomplish this?  If so, what are the other adverse
> implications of protecting a workbook?  I don't want a cure worse than
> the disease.</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2>Thanks!</FONT></DIV>
> <DIV><FONT face=Arial size=2>Dean</FONT></DIV></BODY></HTML>
>
> ------=_NextPart_000_0011_01C6F3AE.02124A50--

Charles Chickering

unread,

Oct 19, 2006, 11:41:01 PM10/19/06

to

Dean another option is to view the properties in the VBA window and set the
Visible property to xlSheetVeryHidden, however if the other users know this
they can do the same thing to unhide them, this does remove the sheet from
the "Window" "Unhide" menu in Excel at least.
--
Charles Chickering

"A good example is twice the value of good advice."

anony mouse

unread,

Oct 20, 2006, 6:45:43 PM10/20/06

to

I struggled over a similar issue last year- I hid all the worksheets and
left a simple opening spreadsheet visible letting the user know that if they
opened up the workbook with macros disabled- they could not access anything.
If macros were enabled- the spreadsheets were password unprotected and
unhidden and as soon as the save or save as was selected- the spreadsheets
were write protected. (This was to preserve the 'template' and prevent
users from taking an old form and adding new data to it- which was often
wrong or incomplete- was better for the person to start from scratch and
answer every field than to change only a few and have other categories
wrong- imagine the previous phone number or email and wrong information
going to the wrong party- was far better to have no phone or email than the
previous form's data.....

The macros work like a charm. Spreadsheets stay hidden and protected as
required and can't be interfered with unless the designer opens them up with
macros turned off, unlocks the spreadsheet, unhides forms, etc.

It was a monster to program (not a vba expert here) but it was fun!

Paul
"Dean" <whooshb...@adelphia.net> wrote in message
news:89GdnZnF2599taXY...@adelphia.com...

gimme_this...@yahoo.com

unread,

Oct 20, 2006, 6:59:56 PM10/20/06

to

Any Excel document can be reverse engineered by a talented Excel
programmer.

The tips here are good ones.

My approach:

1. Make the fonts white so that the worksheet looks empty to a novice
2. Add a sub that makes it so that when someone clicks on a cell that
they get sent to range A1 - That keeps someone from tabbing through the
cells and reading content from the formula bar.
3. Password protect the sheet.
4. Hide the sheet.

Keep in mind. The BEST way to protect data is to never store it in the
Workbook to begin with. Put the sensitive data in a Worksheet that
exists in a plugin.

Dean

unread,

Oct 20, 2006, 7:44:39 PM10/20/06

to

I may not understand your answer. My fear is that someone who gets my
workbook will know enough to unhide the worksheets we don't want them to be
able to see. The designation as a "very hidden" worksheet posted just after
yours sounds quite promising but, if I don't use that, I want to understand
what you are saying.

It seems like your approach won't stop them from unhiding the worksheet. It
just is an easy way to rehide it, if anyone wants to. That is not my
problem. If this will, indeed, automatically rehide the sheet every time
they try to unhide it, then I need to understand how to assign that to only
the hidden sheets. Kindly confirm/explain.

<runde...@gi.konicaminolta.us> wrote in message
news:1161312444....@i3g2000cwc.googlegroups.com...

Dean

unread,

Oct 20, 2006, 7:58:01 PM10/20/06

to

This seem great since it doesn't even allow them to see if something is
hidden. I am wondering if I should have it installed always, just to be
safe, in which case, if my macro needs to hide some sheets, it needs to turn
it off. so it can hide some sheets, then turn it back on. Or else, only put
it there after the sheets are hidden by the macro, in which case, the
protection will be installed only when the macro is invoked.

Is there a downside to workbook protection?

D

Dean
"Dave Peterson" <pete...@verizonXSPAM.net> wrote in message
news:45382A92...@verizonXSPAM.net...

Dean

unread,

Oct 20, 2006, 7:59:44 PM10/20/06

to

This seems really nice but I would need it to be done by the macro that
decides whether to hide any sheets. Is that too hard?

Thanks!
D

"Charles Chickering" <CharlesC...@discussions.microsoft.com> wrote in
message news:F2FD700F-BC4E-4F75...@microsoft.com...

Dean

unread,

Oct 20, 2006, 8:12:07 PM10/20/06

to

Yes, I hear what you are saying about reverse engineering. I am worried
about the not too talented, but non-novice, programmer - someone about as
good as I was before I started talking to all you folks! The "very hidden"
idea, as one example, is probably enough to stymie such a user. I also think
that this kind of user could find my password within a macro, so I am trying
to avoid that, in another thread that I started just when I started this
one.

My client doesn't want every user to know of all the different deal options
that preferred clients of theirs sometimes get, hence the hiding of sheets,
etc. Also, some stuff will only confuse them - things like that, not the
compromise of data, per se, so I'm not sure the plug-in approach would help,
which is good because I have no idea what that is!!

I seem to recall that the white font still shows when you highlight the
cell, or something like that, something even a novice might notice.

But your tips are excellent. Thanks!
Dean

<gimme_this...@yahoo.com> wrote in message
news:1161385196.5...@e3g2000cwe.googlegroups.com...

Dave Peterson

unread,

Oct 21, 2006, 12:05:41 AM10/21/06

to

The downside is that there are things that can't be done in a protected
workbook. But that's it's upside, too.

--

Dave Peterson

gimme_this...@yahoo.com

unread,

Oct 21, 2006, 1:25:15 AM10/21/06

to

Put the data somewhere after row 500.

Dean

unread,

Oct 21, 2006, 1:29:53 AM10/21/06

to

Ok, thanks.

"Dave Peterson" <pete...@verizonXSPAM.net> wrote in message

news:45399C95...@verizonXSPAM.net...

gimme_this...@yahoo.com

unread,

Oct 21, 2006, 2:15:52 AM10/21/06

to

Absolutely - no matter what - do not put that kind of information into
the distributed worksheet.

You, or your employer, will live to regret it.

I did that once, I worked for a big 5 consulting firm - I didn't know
about AddIns. My Workbook application priced health care plans. A
client broke into the Worksheet and started a competing business. My
customer got fired and I got laid off.

Learn how to use AddIns. They aren't that complicated and that's why
Excel created AddIns - precisely for this sort of thing.

Put those hidden sheets into the AddIn.

Look up AddIn an any of Walkenbach's texts. Two or three pages.