Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 213
  • Last Modified:

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

0
ImageryGrl
Asked:
ImageryGrl
  • 6
  • 4
1 Solution
 
andymacfCommented:
Could you paste a sample of your spreadsheet so we can see what we are dealing with?
0
 
ImageryGrlAuthor Commented:
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
0
 
andymacfCommented:
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ImageryGrlAuthor Commented:
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.  
0
 
andymacfCommented:
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.
0
 
ImageryGrlAuthor Commented:
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.  
0
 
andymacfCommented:
Hi there

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

Might be worth having a look at

Andy
0
 
ImageryGrlAuthor Commented:
I actually found out that the original code will work under 2 conditions:
For the first sheet of the file,
do NOT protect cells
do not have the macro apply.

If you do this, and put the macro in any tabs (behind the first) which need it, the file works fine.  
0
 
ImageryGrlAuthor Commented:
Moderator, please close question.
0
 
Rory ArchibaldCommented:
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.
0
 
ImageryGrlAuthor Commented:
I found the answer to the question - see comment in post
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now