Amazing chart utilities from Jon Peltier
Page 1 of 5 123 ... LastLast
Results 1 to 10 of 42

Thread: Using Group/Ungroup in protected worksheets

  1. #1
    Board Regular
    Join Date
    Aug 2002
    Posts
    145
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Using Group/Ungroup in protected worksheets

    Hello,

    I'm trying to protect a worksheet so that cells are protected but users can still group & ungroup sub-totals by using the Group/Ungroup symbols that appear at the margins.

    When using the Protect command, I can't see a way to allow this to happen.

    Does anyone know how this can be done for Excel 2003/2007 ?

    Thanks

  2. #2
    Administrator starl's Avatar
    Join Date
    Aug 2002
    Location
    Everywhere!
    Posts
    5,834
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using Group/Ungroup in protected worksheets

    You would have to use a Workbook_Open macro to set the protection a special way when you open the workbook. The following needs to go in the ThisWorkbook module. The password is password - you can change the text between the quotation marks to what you need it to be. The sheet with the subtotaling is called sales - you can change that below.

    Code:
    Private Sub Workbook_Open()
    With Worksheets("sales")
           .Protect Password:="password", Userinterfaceonly:=True
           .EnableOutlining = True
    End With
    End Sub
    The 3rd item at the following site will show you how to put the code in the ThisWorkbook module.
    http://www.contextures.com/xlvba01.html
    Last edited by starl; Apr 27th, 2009 at 02:50 PM.

  3. #3
    Board Regular
    Join Date
    Aug 2002
    Posts
    145
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using Group/Ungroup in protected worksheets

    Thanks Tracy,

    That's exactly what I need.

  4. #4
    New Member
    Join Date
    Jun 2009
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using Group/Ungroup in protected worksheets

    Hello- I really need to know how to do this in Excel 2007. I have found where to edit the code in the VisualBasic tab (after putting the developer tab on the ribon)- but the code isn't working....help! I really need to be able to group and ungroup despite having protected a few cells.

  5. #5
    Board Regular
    Join Date
    Aug 2002
    Posts
    145
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using Group/Ungroup in protected worksheets

    Hi monkeysonmac,

    Welcome to the board.

    I just tested Tracy's solution on a 2007 workbook & it all works exactly as expected.

    Are you familiar with VBA ?

    If so, check that the auto_open sub is working by including a message box in it. Also, check that the sheet name mentioned in the sub is the same as the sheet name you are using.

    If not, follow the directions in Tracy's web link.

    If you want to pm me, I'll send you the small spreadsheet that I've set up.

  6. #6
    New Member
    Join Date
    Jun 2009
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using Group/Ungroup in protected worksheets

    thanks for the quick reply- yes, I'm still a bit stumped....I hav eprivate messaged you...I think i can figure it out based on a sample...thanks again

  7. #7
    Board Regular
    Join Date
    Aug 2002
    Posts
    145
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using Group/Ungroup in protected worksheets

    Have sent you a pm with the sample workbook.

  8. #8
    New Member
    Join Date
    Jun 2009
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using Group/Ungroup in protected worksheets

    thanks for the help! you were right- the sequence seemed to be messing me up. Also, The macros weren't running automatically so I had to enable them.

    Per your note:

    I was thinking a bit about the sequence .....

    Go to the worksheet, unprotect it, set up the sub-totals & then close/reopen to get this working.
    Also, Even though I was protecting cells, I wanted to be able to show the formulas when the protected cell was selected. If anyone is reading this and wants to know how to do that:
    1. unprotect the worksheet
    2. select the clls you want to show formulas on even when protected
    3. Format>Format Cells>Protection> uncheck the "hidden" box.
    Thanks again for your help!

  9. #9
    MrExcel MVP Tazguy37's Avatar
    Join Date
    May 2004
    Location
    Aurora, Colorado
    Posts
    4,237
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using Group/Ungroup in protected worksheets

    How do you do this through the UI in Excel 2007? I can't find an option for it when I protect the sheet, or in Excel Options. The posted VBA code works fine, but if a non-VBA solution is available, that's preferable.

    Todd
    Todd Seward
    Building Successful Technical Solutions

    Learn something new every day!


    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  10. #10
    New Member
    Join Date
    Mar 2011
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using Group/Ungroup in protected worksheets

    I have used the macro that is suggested and it works succefully. However what I have found is that it allows the user to enter information in unprotected cells but it does not allow the user to enter cell comments.

    I have set up a template report that needs to be completed by other users. I do not want the report structure changed and so I have protected the sheet. Because it requires grouping and ungrouping I have used the macro given.
    However, I would like for the user to be able to insert a cell comment to explain the number entered.
    The macro shown does not allow for this.

    Can anyone please help me?

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •