Using Group/Ungroup in protected worksheets

Linus_99

Board Regular
Joined
Aug 28, 2002
Messages
145
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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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:
Upvote 0
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. :(:confused:
 
Upvote 0
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.
 
Upvote 0
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:)
 
Upvote 0
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-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
Go to the worksheet, unprotect it, set up the sub-totals & then close/reopen to get this working.<o:p></o:p>
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!
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top