Is there a way to protect worksheets from being viewed at all, not password
protected from modifications only?
I have a workbook set up with 9 spreadsheets that will be specific to
certain users, I do not want all users/viewers to be able to see each
worksheet, just the one labelled for them?
I am working with excel 97 and 8 of the 9 spreadsheets are all linked to the
master spreadsheet within that one workbook. I am trying to avoid having to
create individual workbooks.
thier is propaply a tider way to do this but something like
Private Sub Auto_Open()
Sheets("viewer 1").Visible = False
Sheets("viewer 2").Visible = False
Sheets("viewer 3").Visible = False
Sheets("viewer 4").Visible = False
Sheets("viewer 5").Visible = False
Sheets("viewer 6").Visible = False
Sheets("viewer 7").Visible = False
Sheets("viewer 8").Visible = False
Sheets("viewer 9").Visible = False
resp = InputBox("user password")
If resp = "password 1" Then Sheets("viewer 1").Visible = True
If resp = "password 2" Then Sheets("viewer 2").Visible = True
If resp = "password 3" Then Sheets("viewer 3").Visible = True
If resp = "password 4" Then Sheets("viewer 4").Visible = True
If resp = "password 5" Then Sheets("viewer 5").Visible = True
If resp = "password 6" Then Sheets("viewer 6").Visible = True
If resp = "password 7" Then Sheets("viewer 7").Visible = True
If resp = "password 8" Then Sheets("viewer 8").Visible = True
If resp = "password 9" Then Sheets("viewer 9").Visible = True
End Sub
If your users have enough savvy to find these newsgroups, they'll find
that they can unprotect any workbook using widely available methods. For
instance, see:
http://www.mcgimpsey.com/excel/removepwords.html
and
http://www.mcgimpsey.com/excel/fileandvbapwords.html
If instead they aren't likely to have even a modicum of curiosity, you
can find many suggestions in the archives:
http://groups.google.com/advanced_gr...ugroup=*excel*
In article <[email protected]>,
tmack2511 <[email protected]> wrote:
> Is there a way to protect worksheets from being viewed at all, not password
> protected from modifications only?
> I have a workbook set up with 9 spreadsheets that will be specific to
> certain users, I do not want all users/viewers to be able to see each
> worksheet, just the one labelled for them?
> I am working with excel 97 and 8 of the 9 spreadsheets are all linked to the
> master spreadsheet within that one workbook. I am trying to avoid having to
> create individual workbooks.
true but your statment is a little obvious who ever said excell or any other
computer program was fool proof , the simple fact of the matter is most pc
users ony know how to do what they are shown to do on a computer and TMACK
will know his users abilitys better than you or i,
"JE McGimpsey" wrote:
> If your users have enough savvy to find these newsgroups, they'll find
> that they can unprotect any workbook using widely available methods. For
> instance, see:
>
> http://www.mcgimpsey.com/excel/removepwords.html
>
> and
>
> http://www.mcgimpsey.com/excel/fileandvbapwords.html
>
> If instead they aren't likely to have even a modicum of curiosity, you
> can find many suggestions in the archives:
>
> http://groups.google.com/advanced_gr...ugroup=*excel*
>
>
> In article <[email protected]>,
> tmack2511 <[email protected]> wrote:
>
> > Is there a way to protect worksheets from being viewed at all, not password
> > protected from modifications only?
> > I have a workbook set up with 9 spreadsheets that will be specific to
> > certain users, I do not want all users/viewers to be able to see each
> > worksheet, just the one labelled for them?
> > I am working with excel 97 and 8 of the 9 spreadsheets are all linked to the
> > master spreadsheet within that one workbook. I am trying to avoid having to
> > create individual workbooks.
>
On Mon, 2 Jan 2006 22:17:02 -0800, "Rich" <[email protected]> wrote:
>true but your statment is a little obvious who ever said excell or any other
>computer program was fool proof , the simple fact of the matter is most pc
>users ony know how to do what they are shown to do on a computer and TMACK
>will know his users abilitys better than you or i,
Actually JE's post is spot on.
This ain't 1985 any more. Users *aren't* awed by the mystical beige
box which runs unfathomable programs. They tend to read about how to
use their applications better. They learn things from other users.
They are NOT dependent on the office guru to teach them how to do
everything, and they're often prepared to learn things on their own,
those rascals. The first one to learn how to use Google (and I think
that applies to not less than 90% of the users who can find the "On"
button) and figure out how to type in "Crack Excel Password" will be
rewarded with whatever secrets tmack wants to keep.
You don't need to know any advanced coding to do this. You can copy
and paste from any number of postings which contain the relevant code.
The users don't know how to create a VBA macro? Fine, but I'll bet
they know how to use a menu item. Rob Bovey's Excel Utilities add-in,
which is freely downloadable, has "Break Passwords" right there on the
menu bar.
We're not talking about something that's merely "not foolproof". We're
talking about a joke of a "security" restriction which yields as
easily as a peanut does to a sledgehammer.
Speaking of which... I don't think that your code is such a good idea
either.
Sheets("viewer 1").Visible = False
will indeed hide the sheet, but the user can make ALL of the sheets
visible again by going to Format -> Sheet -> Unhide. Each and every
hidden sheet will be only a click or two away.
(This of course presupposes that the user doesn't disable the macros
before opening the workbook. If you rely on the Auto_Open() macro to
HIDE the sheets (and bear in mind that your code will error out unless
there's at least ONE sheet still visible), then all they need to do is
disable the macros and everything will open unhidden. You would need
to reverse the process to have any kind of security at all; that is,
hide all of the sheets with data before you save it (leaving only a
"cover sheet" visible), then have the Auto_Open() macro or the
Workbook_Open event UNHIDE the relevant sheet. You'd then need to have
the Workbook_BeforeClose event RE-HIDE that sheet AND re-save the
workbook. And even that can be gotten around if one is sufficiently
devious.)
You can get a LITTLE more security by having the sheets' visible
property set to xlSheetVeryHidden instead of False, since that will at
least prevent them from appearing in the Format -> Sheet -> Unhide
menu item. The VBA project would also need to be protected to ensure
that the user couldn't make them visible through there. But again, as
JE rightly states, this is not exceptionally strong security. It will
prevent casual hacking, but that's about it.
A secured Access database provides more security, but again I wouldn't
trust it with anything that was seriously confidential. Let's put it
this way; if someone has access to a data file, then they can crack
it. Period. Going the route of having separate workbooks with separate
opening passwords (preferably with each user's file stored on a
network drive that only they and you have access to) is about as
secure as you can get, which is to say, not exceptionally. If it's
something that really, REALLY needs security, and I'm thinking here of
employee pay records and the like, the only real way to do it is to
bite the bullet and go the whole hog (pardon the mixed metaphore) with
something like an SQL*Server client / server setup. That is, where the
data files are hidden away from the users, and only the relevant
chunks of data get fed to the client.
Whether this is worth the time and expense in any particular case is
left as an exercise for the individual user.
>"JE McGimpsey" wrote:
>
>> If your users have enough savvy to find these newsgroups, they'll find
>> that they can unprotect any workbook using widely available methods. For
>> instance, see:
>>
>> http://www.mcgimpsey.com/excel/removepwords.html
>>
>> and
>>
>> http://www.mcgimpsey.com/excel/fileandvbapwords.html
>>
>> If instead they aren't likely to have even a modicum of curiosity, you
>> can find many suggestions in the archives:
>>
>> http://groups.google.com/advanced_gr...ugroup=*excel*
>>
>>
>> In article <[email protected]>,
>> tmack2511 <[email protected]> wrote:
>>
>> > Is there a way to protect worksheets from being viewed at all, not password
>> > protected from modifications only?
>> > I have a workbook set up with 9 spreadsheets that will be specific to
>> > certain users, I do not want all users/viewers to be able to see each
>> > worksheet, just the one labelled for them?
>> > I am working with excel 97 and 8 of the 9 spreadsheets are all linked to the
>> > master spreadsheet within that one workbook. I am trying to avoid having to
>> > create individual workbooks.
---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
* Please keep all replies in this Newsgroup. Thanks! *
Well, in my experience, it's not obvious at all - while nobody expects
XL to be foolproof (since better fools are invented daily), lots of
people overestimate XL's protection (in part due to the self-serving
information that MS puts in the protection dialogs and Help).
OTOH, while you may be correct about most pc users (I'm primarily on a
Mac), I think you significantly underestimate the ability, and
inclination, of many users to use simple methods to access protected
content.
And yes, tmack2511 will know his users' abilities better than you or I,
which is why I linked to the archives, where s/he can choose among the
bad options.
FWIW, the code you offered, in addition to being easily bypassed, will
not be at all secure. All the user will have to do is choose
Format/Sheet/Unhide in order to see everyone else's sheet.
There are ways that you can beef up your code, but all of them can be
foiled with easily accessible techniques.
In article <[email protected]>,
"Rich" <[email protected]> wrote:
> true but your statment is a little obvious who ever said excell or any other
> computer program was fool proof , the simple fact of the matter is most pc
> users ony know how to do what they are shown to do on a computer and TMACK
> will know his users abilitys better than you or i,
>
I once heard the saying 'Don't store anything on a computer that you wouldn't write on the back of a postcard and send to the other side of the world.'
At the end of the day, if someone makes the best use of the resources available, then what more can they do. Computers are too powerful not to be taken advantage of, and there's always an element of risk, but should we turn our backs because of this?
Not in my opinion, I'm currently building an Excel based register and markbook system for teachers. I have put the standard copyright blurb on the workbook and will password up certain bits of it. This wouldn't stop someone who wanted to steal the content, code, design etc....but this isn't going to stop me builfding it in the first place.
Minimise the risk as best we can and go for it!
Cheers,
Mark.
In article <[email protected]>,
mevetts <[email protected]> wrote:
> At the end of the day, if someone makes the best use of the resources
> available, then what more can they do. Computers are too powerful not
> to be taken advantage of, and there's always an element of risk, but
> should we turn our backs because of this? Not in my opinion,
I don't think anyone is suggesting turning a back on technology. But
it's dumb, IMO, to risk distributing private or confidential information
to unauthorized parties when it's well known that the protection for
that information is compromised.
No business I've ever worked with has chosen to ignore that preventable
risk. Businesses that do will deservedly fail.
It makes far more sense to take further advantage of the technology -
perhaps using VBA to create separate files to be sent to each worker,
and to then consolidate any changes.
> I'm currently building an Excel based register and
> markbook system for teachers. I have put the standard copyright blurb
> on the workbook and will password up certain bits of it. This
> wouldn't stop someone who wanted to steal the content, code, design
> etc....but this isn't going to stop me builfding it in the first
> place.
The only thing I use worksheet or workbook protection for in my
commercial XL work is to minimize the opportunity for users to
inadvertently muck up the worksheet structure and formulae. I rarely use
a password unless the client insists on it (after I let them know that
it won't prevent a 6th grader from bypassing it - I know because my 6th
grader independently found the info and used it to remove his teacher's
worksheet protection after the teacher forgot the password).
I figure that the best protection of my work is to provide an excellent
product that clients will pay for. So far, that's worked very well. I
know my copyright has often been violated (the first time was a business
app suite for the CBM8000 back in 1979), but I tell myself that the
violators probably wouldn't have paid for it anyway. Though a couple of
them have, after I've suggested that my fee was somewhat cheaper than
their lawyer's.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks