Using Group/Ungroup in protected worksheets

[Linus_99]

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

 

[starl]

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.

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

 

[Linus_99]

Thanks Tracy,

That's exactly what I need.

 

[monkeysonmac]

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.

 

[Linus_99]

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.

 

[monkeysonmac]

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

 

[Linus_99]

Have sent you a pm with the sample workbook.

 

[monkeysonmac]

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 .....<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Go to the worksheet, unprotect it, set up the sub-totals & then close/reopen to get this working.<o></o>
​

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!

 

[Tazguy37]

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

 

[mgb64]

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?