protect worksheets in workbook

Discussion in 'Microsoft Excel Worksheet Functions' started by Guest, Sep 14, 2006.

  1. Guest

    Guest Guest

    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...
     
    Guest, Sep 14, 2006
    #1
    1. Advertisements

  2. Guest

    Guest Guest

    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...
     
    Guest, Sep 14, 2006
    #2
    1. Advertisements

  3. Guest

    Guest Guest

    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...
     
    Guest, Sep 14, 2006
    #3
  4. Guest

    Gord Dibben Guest

    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 <> 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...
     
    Gord Dibben, Sep 14, 2006
    #4
  5. Guest

    JE McGimpsey Guest

    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 <>,
    Boo <> 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.
     
    JE McGimpsey, Sep 15, 2006
    #5
  6. Guest

    Guest Guest

    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 <> 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...

    >
    >
     
    Guest, Sep 25, 2007
    #6
  7. Guest

    Gord Dibben Guest

    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 <> 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 <> 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...

    >>
    >>
     
    Gord Dibben, Sep 26, 2007
    #7
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Geneva Sachs

    Need to 3 sorts (3 worksheets) in one workbook and then to another workbook!

    Geneva Sachs, Jan 29, 2004, in forum: Microsoft Excel Worksheet Functions
    Replies:
    0
    Views:
    162
    Geneva Sachs
    Jan 29, 2004
  2. Guest

    Password protect worksheets from being viewed within a workbook

    Guest, Sep 7, 2004, in forum: Microsoft Excel Worksheet Functions
    Replies:
    1
    Views:
    342
    agarwaldvk
    Sep 7, 2004
  3. Guest

    how do I to link workbook B and workbook C to workbook A?

    Guest, Oct 13, 2004, in forum: Microsoft Excel Worksheet Functions
    Replies:
    1
    Views:
    279
    Frank Kabel
    Oct 13, 2004
  4. Guest

    Reference External Worksheets & Protect Worksheet / Workbook

    Guest, Jan 9, 2006, in forum: Microsoft Excel Worksheet Functions
    Replies:
    0
    Views:
    170
    Guest
    Jan 9, 2006
  5. Gator Girl

    Protect or Hide all worksheets in a workbook except 1

    Gator Girl, Feb 3, 2009, in forum: Microsoft Excel Worksheet Functions
    Replies:
    2
    Views:
    264
    Gord Dibben
    Feb 4, 2009
Loading...

Share This Page