Grouping in a Protected Worksheet

Thu, 10/01/2009 - 12: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

‹ Hyperlinks Custom Menu ›
Thu, 10/01/2009 - 12: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 - 12: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 - 12: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 - 12: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

—

Grouping in a Protected Worksheet