asked on

Need to use row and column groupings on protected sheet

I need to protect certain cells of an Excel 2010 sheet. I also need to enable users to Expand/Collapse Row and Column groupings. I searched and found the code below. The code was placed in the worksheet for which I would like the protection with groupings enabled. It did not work.

My files are supposed to go out today so 500 pts if someone can help me get this nailed.

Thanks in advance!!

Private Sub Worksheet_Activate() 
With Me 
.Protect Password:="Lockthis", userinterfaceonly:=True 
.EnableOutlining = True 
End With 
End Sub

Open in new window

Andrew

Could you paste a sample of your spreadsheet so we can see what we are dealing with?

ImageryGrl

ASKER

Here is a mockup of the issue. while the file is protected, both rows and columns should be able to be expanded and collapsed
Protection-with-Grouping-Enabled.xlsm

Andrew

Have you tried this, http://office.microsoft.com/en-us/excel-help/lock-only-a-few-cells-on-a-worksheet-HA001054825.aspx, where you effectively unlock certain cells and then the sheet is protected it only protects the cells you need to protect

http://office.microsoft.com/en-us/excel-help/password-protect-worksheet-or-workbook-elements-HP005201059.aspx?CTT=5&origin=HA001054825

ImageryGrl

ASKER

Yes, we employ selective unlocking of cells in the sheet. The problem is not HOW to lock the cells or HOW to group the rows and columns, the problem is how to use the Grouped Rows and Columns on a password protected file.

Andrew

I found this article that might help:

http://www.pcreview.co.uk/forums/do-enable-group-and-ungroup-protected-sheet-t1054601.html

The main theme of which is as follows:

If you protect the worksheet programmatically, you can enable outlining,
and you will be able to use the groups that you have created.

The following code goes in the ThisWorkbook module:

Private Sub Workbook_Open()
With Worksheets("Sheet1")
.EnableOutlining = True
.Protect Password:="password", _
Contents:=True, UserInterfaceOnly:=True
End With
End Sub

To paste the code into the ThisWorkbook module:

Right-click on the Excel icon, to the left of the File menu
Choose View Code
Paste the code where the cursor is flashing.

ImageryGrl

ASKER

This is exactly the same code which I posted in my initial post, which we have not gotten to work.
If you can make it work in my sample spreadsheet, I would be most grateful.

Andrew

Hi there

I have found another EE on this topic: ID: 26467381

Might be worth having a look at

Andy

ASKER CERTIFIED SOLUTION

ImageryGrl

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ImageryGrl

ASKER

Moderator, please close question.

Rory Archibald

Just FYI the code doesn't run on the sheet that is active when you open the workbook - note that this may or may not be the first sheet in the workbook, depending on how it was saved. It would, in my opinion, be far better to use the Workbook_Open event to protect all sheets that require it when the workbook opens, rather than changing the protection each time you switch between sheets, which is unnecessary.

ImageryGrl

ASKER

I found the answer to the question - see comment in post