PC Review


Reply
Thread Tools Rate Thread

protect worksheets in workbook

 
 
=?Utf-8?B?QmFyYg==?=
Guest
Posts: n/a
 
      14th Sep 2006
I have a football pool spreadsheet that has 20 'sheets', 1 for each person in
the pool. The 1st sheet has a combined summary of who has bet on what teams.
This will be hidden and seen only by me, since I am updating the final
scores. The rest of the sheets need to be protected so that only that person
can open their sheet. I know I can protect the workbook and protect the
worksheets, but this still allows everyone to 'view' everyone else's
worksheet.

Is there some way to protect the sheet so it can't be viewed unless with a
password? I'm not comfortable with VBA code, so please don't offer that
solution, although I'm sure it will be the best answer.
Thanks...
 
Reply With Quote
 
 
 
 
=?Utf-8?B?Qm9v?=
Guest
Posts: n/a
 
      14th Sep 2006
Barb,

Not a very elegant solution but one that suits your needs without going down
the VBA route...

1. Hide the rows containg data on each persons sheet
2. Then Tools > Protection > Protect Sheet
3. Use a different password for each sheet and pass this on to each person

This way, only the person with the password can unprotect their own sheet
and unhide the rows to view/update data.

As I say...much better to use VBA but this would work.

As a side note, you probably only want the person to be able to view their
sheet and not make changes (unless you have very honest friends!) - you can
do this by protecting the cells from update (once the person has completed
their entry) but keeping this password to yourself

"Barb" wrote:

> I have a football pool spreadsheet that has 20 'sheets', 1 for each person in
> the pool. The 1st sheet has a combined summary of who has bet on what teams.
> This will be hidden and seen only by me, since I am updating the final
> scores. The rest of the sheets need to be protected so that only that person
> can open their sheet. I know I can protect the workbook and protect the
> worksheets, but this still allows everyone to 'view' everyone else's
> worksheet.
>
> Is there some way to protect the sheet so it can't be viewed unless with a
> password? I'm not comfortable with VBA code, so please don't offer that
> solution, although I'm sure it will be the best answer.
> Thanks...

 
Reply With Quote
 
 
 
 
=?Utf-8?B?QmFyYg==?=
Guest
Posts: n/a
 
      14th Sep 2006
Boo, the 1st part works great, however, how do I password protect the sheet
after they've made their selections with a different password so they can
make any additional changes? Do I just 'lock' the cells, but wouldn't they
be able to 'unlock' them with the password?
Thanks...

"Boo" wrote:

> Barb,
>
> Not a very elegant solution but one that suits your needs without going down
> the VBA route...
>
> 1. Hide the rows containg data on each persons sheet
> 2. Then Tools > Protection > Protect Sheet
> 3. Use a different password for each sheet and pass this on to each person
>
> This way, only the person with the password can unprotect their own sheet
> and unhide the rows to view/update data.
>
> As I say...much better to use VBA but this would work.
>
> As a side note, you probably only want the person to be able to view their
> sheet and not make changes (unless you have very honest friends!) - you can
> do this by protecting the cells from update (once the person has completed
> their entry) but keeping this password to yourself
>
> "Barb" wrote:
>
> > I have a football pool spreadsheet that has 20 'sheets', 1 for each person in
> > the pool. The 1st sheet has a combined summary of who has bet on what teams.
> > This will be hidden and seen only by me, since I am updating the final
> > scores. The rest of the sheets need to be protected so that only that person
> > can open their sheet. I know I can protect the workbook and protect the
> > worksheets, but this still allows everyone to 'view' everyone else's
> > worksheet.
> >
> > Is there some way to protect the sheet so it can't be viewed unless with a
> > password? I'm not comfortable with VBA code, so please don't offer that
> > solution, although I'm sure it will be the best answer.
> > Thanks...

 
Reply With Quote
 
Gord Dibben
Guest
Posts: n/a
 
      14th Sep 2006
Barb

The sheets need to be hidden and the workbook structure protected.

Unfortunately, unless the sheets are veryhidden(VBA) unprotecting the workbook
will allow anyone to unhide any sheet.

Without VBA I would suggest you move each person's worksheet to a new workbook.

You can link your summary workbook to the others and each workbook will have its
own password to open.


Gord Dibben MS Excel MVP

On Thu, 14 Sep 2006 13:34:02 -0700, Barb <(E-Mail Removed)> wrote:

>I have a football pool spreadsheet that has 20 'sheets', 1 for each person in
>the pool. The 1st sheet has a combined summary of who has bet on what teams.
> This will be hidden and seen only by me, since I am updating the final
>scores. The rest of the sheets need to be protected so that only that person
>can open their sheet. I know I can protect the workbook and protect the
>worksheets, but this still allows everyone to 'view' everyone else's
>worksheet.
>
>Is there some way to protect the sheet so it can't be viewed unless with a
>password? I'm not comfortable with VBA code, so please don't offer that
>solution, although I'm sure it will be the best answer.
>Thanks...


 
Reply With Quote
 
JE McGimpsey
Guest
Posts: n/a
 
      15th Sep 2006
However, note that it's absolutely trivial to see the contents of the
hidden rows without knowing the password:

Say you want to see what's on sheet "Bob". Create a blank sheet, and
enter this in cell A1:

=Bob!A1

Copy down and across as far as necessary. You'll see everything on "Bob".

Of course, unprotecting a sheet is also absolutely trivial:

http://www.mcgimpsey.com/excel/removepwords.html




In article <(E-Mail Removed)>,
Boo <(E-Mail Removed)> wrote:

> 1. Hide the rows containg data on each persons sheet
> 2. Then Tools > Protection > Protect Sheet
> 3. Use a different password for each sheet and pass this on to each person
>
> This way, only the person with the password can unprotect their own sheet
> and unhide the rows to view/update data.

 
Reply With Quote
 
=?Utf-8?B?QXJvbg==?=
Guest
Posts: n/a
 
      25th Sep 2007
Hey!


This theme is relevant for me as well. I like the idea about making a
summary woorkbook with links. But how does it work?


Thanks!

"Gord Dibben" skrev:

> Barb
>
> The sheets need to be hidden and the workbook structure protected.
>
> Unfortunately, unless the sheets are veryhidden(VBA) unprotecting the workbook
> will allow anyone to unhide any sheet.
>
> Without VBA I would suggest you move each person's worksheet to a new workbook.
>
> You can link your summary workbook to the others and each workbook will have its
> own password to open.
>
>
> Gord Dibben MS Excel MVP
>
> On Thu, 14 Sep 2006 13:34:02 -0700, Barb <(E-Mail Removed)> wrote:
>
> >I have a football pool spreadsheet that has 20 'sheets', 1 for each person in
> >the pool. The 1st sheet has a combined summary of who has bet on what teams.
> > This will be hidden and seen only by me, since I am updating the final
> >scores. The rest of the sheets need to be protected so that only that person
> >can open their sheet. I know I can protect the workbook and protect the
> >worksheets, but this still allows everyone to 'view' everyone else's
> >worksheet.
> >
> >Is there some way to protect the sheet so it can't be viewed unless with a
> >password? I'm not comfortable with VBA code, so please don't offer that
> >solution, although I'm sure it will be the best answer.
> >Thanks...

>
>

 
Reply With Quote
 
Gord Dibben
Guest
Posts: n/a
 
      26th Sep 2007
See help on "linking" then click on "about linking to another workbook or
program"


Gord

On Tue, 25 Sep 2007 14:10:00 -0700, Aron <(E-Mail Removed)> wrote:

>Hey!
>
>
>This theme is relevant for me as well. I like the idea about making a
>summary woorkbook with links. But how does it work?
>
>
>Thanks!
>
>"Gord Dibben" skrev:
>
>> Barb
>>
>> The sheets need to be hidden and the workbook structure protected.
>>
>> Unfortunately, unless the sheets are veryhidden(VBA) unprotecting the workbook
>> will allow anyone to unhide any sheet.
>>
>> Without VBA I would suggest you move each person's worksheet to a new workbook.
>>
>> You can link your summary workbook to the others and each workbook will have its
>> own password to open.
>>
>>
>> Gord Dibben MS Excel MVP
>>
>> On Thu, 14 Sep 2006 13:34:02 -0700, Barb <(E-Mail Removed)> wrote:
>>
>> >I have a football pool spreadsheet that has 20 'sheets', 1 for each person in
>> >the pool. The 1st sheet has a combined summary of who has bet on what teams.
>> > This will be hidden and seen only by me, since I am updating the final
>> >scores. The rest of the sheets need to be protected so that only that person
>> >can open their sheet. I know I can protect the workbook and protect the
>> >worksheets, but this still allows everyone to 'view' everyone else's
>> >worksheet.
>> >
>> >Is there some way to protect the sheet so it can't be viewed unless with a
>> >password? I'm not comfortable with VBA code, so please don't offer that
>> >solution, although I'm sure it will be the best answer.
>> >Thanks...

>>
>>


 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Re: Disable Tools, Protect, Protect Workbook Ron de Bruin Microsoft Excel Programming 0 5th Sep 2005 03:37 PM
Can protect worksheet then workbook but not Protect and Share in code ctmom@comcast.net Microsoft Excel Programming 5 9th Aug 2005 03:26 PM
Running a macro to protect a workbook on a already protected workbook UNprotects the workbook ?? WimR Microsoft Excel Programming 9 25th Jul 2005 12:44 PM
Can't password protect workbook -- no pw protect box on Tools>Opts =?Utf-8?B?TVM=?= Microsoft Excel Discussion 2 16th Jul 2005 03:04 PM
Excel - protect sheet vs. protect workbook =?Utf-8?B?RGlydGJ5a2VEaXZh?= Microsoft Excel Misc 3 1st Sep 2004 02:10 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 02:33 PM.