Grouping in a Protected Worksheet

Thu, 10/01/2009 - 11:17 — rich

Hi, Jeff. I hope you're doing well. I have what I hope is a very quick question for an Excel guru such as yourself! (I hope you don't mind).

I'm trying to allow grouping/ungrouping in a password protected worksheet(s) that I will be distributed to salespeople. I found the below url last evening, tried it using Excel 2000 (at home) and it worked just fine. Now, this morning, I'm trying it at work where we have Excel 2007, and it will not work. Do you have any suggestions?

Also, on another website, I saw a recommendation to add the "Option Explicit" statement in the beginning of the code, but that didn't seem to help either.

Any suggestions you have would be greatly appreciated.

Regards,

Rich

‹ Welcome Message Macro to combine multiple workbooks into a single workbook ›
Thu, 10/01/2009 - 11:18 — jlenning

Rich,   I would paste the

Rich,
 
I would paste the following code into the "Microsoft Excel Objects > ThisWorkbook" within the Visual Basic Editor; it works perfectly here on my Excel 2007 so you should be fine. All you need to do is change the "With Sheet1" to be "With YourSheetName" for the worksheet in your workbook. The second thing to do is just make sure that the Password:="" uses your password. Remember that a sophisticated Excel user could view the macro to retrieve the password and then unprotect the sheet. So, bear that in mind as you deploy the book with the type of data in there.
 
ALSO, I highly suggest the change I made below because if you use your original code then if the user disables macros then the worksheet remains unprotected. In my revision, the worksheet remains protected, and if the user enables macros then the macro simply changes the protection so that it allows the user to expand/collapse the data grouped rows/columns.
 
Hope that helps!
 
Thanks
Jeff
 
 
 
Private Sub Workbook_Open()
    With Sheet1
        .Unprotect Password:="Secret"
       .Protect Password:="Secret", UserInterfaceOnly:=True
       .EnableOutlining = True
    End With
End Sub
 

—

Thu, 10/01/2009 - 11:19 — rich

Closer

Hey, thanks so much, Jeff. This is my first foray into coding. Maybe my mistake was that I was clicking on Developer, then View Code, and pasting there.

Also, if there is more than one sheet in the workbook, would it look as follows?:

==================
Private Sub Workbook_Open()
With Sheet1 Sheet2 Sheet3
.Unprotect Password:="Secret"
.Protect Password:="Secret", UserInterfaceOnly:=True
.EnableOutlining = True
End With
End Sub
=======================

Rich

—

Thu, 10/01/2009 - 11:20 — jlenning

All Sheets

If you want to cycle through all worksheets in the workbook, the easiest way is to use the following code so that any new sheets are automatically included in the process:
 
Private Sub Workbook_Open()
For Each ws In Sheets
   With ws
       .Unprotect Password:="Secret"
       .Protect Password:="Secret", UserInterfaceOnly:=True
       .EnableOutlining = True
    End With
Next ws
End Sub
 
Thanks
Jeff

—

Thu, 10/01/2009 - 11:20 — rich

It Works!

It works! I never would have figured that one out.

You are truly an Excel genius. Thanks very much.

Take care,

Rich

—

Wed, 10/13/2010 - 12:29 — mbugni

Missing something

Hi Jeff - I pasted the code and changed the password as instructed, but I'm still not able to group/ungroup with the protection on. Could you provide any additional information that would help a novice like me? Thank you.

Mike

Wed, 10/13/2010 - 13:01 — jlenning

Clarification

 Mike - so, just to clarify, you have an Excel file, and one of the worksheets has protection turned on, and you want to enable a user to use the group expand/collapse buttons?  And, then you inserted the above code but it still doesn't work?  If you confirm that you tried the following steps, and then let me know what error messages you get, that would be great:

1 - Open workbook
2 - Open the Visual Basic Editor
3 - Under the VBAProject > Microsoft Excel Objects > ThisWorkbook, paste the code above (the one that begins with Private Sub Workbook_Open())
4 - Go back to Sheet1 (if you used a different sheet name, you'll need to update the macro)
5 - Add your groupings
6 - Turn on worksheet protection
7 - Save the Workbook
8 - CLOSE the workbook
9 - Open the workbook, and be sure that the macros are enabled
10 - try to expand/collapse the groupings

Just ran through these steps here, worked like a charm.

Good luck, and ping me back if you get stuck.
Thanks
Jeff



—

Thu, 10/14/2010 - 06:49 — jlenning

Sheet Name Syntax

 Mike,

 

OK, I see the issue and know how to fix.

 

THE FIX

Replace the line:

      With Sheet1

with the line:

      With Sheets("Sheet1")

 

THE ISSUE

There is a difference between the worksheet name, and the underlying sheet number.  The original macro code used the underlying sheet number, which can only be seen by opening the visual basic editor.  In there you will find your sheet named Sheet1 is actually Sheet9, and your sheet named System is actually Sheet1.  So, the original vba code's syntax referred to the underlying sheet number.  The fix above that uses the sheets("sheetname") syntax refers to the worksheet's name as displayed.

 

I've tested the above change in your file, and it works like a champ.  It also explains why your code seems to protect the hidden system sheet but not the intended sheet.

 

Good luck, and ping me back if you get stuck!

 

Thanks

Jeff

—

Mon, 04/11/2011 - 19:56 — lfalvo

Hi Jeff, The macro works for

Hi Jeff,
The macro works for grouping in a protected sheet. But my workbook is also a 'shared' workbook, and once I reinstate the 'shared' option I can no longer expand/collapse groups. Would you have any fixes for this?
Thanks, Lady

—

Lady

Tue, 04/12/2011 - 07:58 — jlenning

Built-in Limitation

Lady,

Hi there!  Say, once a workbook is shared, it prevents worksheet protection from being changed or turned off, either manually or via a macro.  The macro above essentially turns off worksheet protection and then turns it back on but allows Outlining (expanding grouped rows/columns) each time the workbook is opened.  Once a workbook is closed, the Outlining sets back to False, which is why the macro needs to run each time the workbook is opened.

So, after you share the workbook and then open it back up, the macro runs but fails on the unprotect line, because the macro can't turn off protection.

Thus, if you have lots of concurrent users, and they are all editing (not just reading) and merging changes, I can't think of a workaround.  If however, you have few concurrent users, and they are mostly reading the workbook and not saving and merging changes, then it may work to modify the macro to actually turn off sharing, protect the sheets with outlining enabled, and then reshare the workbook each time it is opened.  However, I'm not sure of the consequences of doing this each time a user opens the book, because it removes the change history, etc.  But, if it is a direction you'd like to explore, I could play with the vba code.

Let me know what you think!

Thanks,
Jeff
 

—

Tue, 08/02/2011 - 02:22 — mafsssg

Thanks

Hi Jeff,

Just writing to thank your help.
I start working with Visual basics last week and I'm really happy I achieved the result I wanted.

Your comments were really helpfull!

Thank you for "wasting your time" making us "gaining ours" (I hope this sentence work in elnglish...).

Best regards,
Mafalda

Mon, 02/06/2012 - 07:23 — cjsaunders

Protection Attributes

Jeff,
This worked perfectly for allowing me to protect my workbook, but still allow users to group/ungroup as needed.
The one issue I have now, is that the other protection attributes that I had selected (Format Cells, Format columns, and format rows) get saved over by the default protection when the macro runs.

Is there a way to include in the code these features so that they will remain?

Mon, 02/06/2012 - 07:42 — jlenning

Protection Atrributes

cjsaunders,

I understand that when you turn on worksheet protection initially, you select additional items from the Protect Sheet dialog box, including for example the Format Cells checkbox, along with the Format Columns and Format Rows checkboxes.  

Since the macro cycles through and turns off protection, and then turns it back on again, you need a way to modify the macro code to include the additional protection attributes.  Fortunately, it is very easy.

You simply list the attributes out in the .Protect line, as follows:

.Protect AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True

So the resulting modified VBA code looks something like this:

Private Sub Workbook_Open()
 For Each ws In Sheets
   With ws
   .Unprotect Password:="Secret"
   .Protect Password:="Secret", UserInterfaceOnly:=True, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True 
   .EnableOutlining = True
   End With
 Next ws
End Sub

To get the VBA code for any attribute, simply start the macro recorder, turn on worksheet protection and select the attributes from the Protect Sheet dialog box, and then stop the recorder.  Then, view the resulting VBA code to see the syntax to use.

Hope this helps...thanks!

 Thanks
Jeff

—

Mon, 02/06/2012 - 08:25 — cjsaunders

Thank you! This worked

Thank you!

This worked perfectly!

Grouping in a Protected Worksheet