Excel 2010 - Collapse/Expand cell when Sheet is Protected

[vbsoco26]

Is there a way to allow a sheet to Collapse or Expand Rows when the page is protected? Apparently selecting Format Column/Rows in the check boxes does not allow this once the page is protected. I have a gorup of protected cells on the page, but need to allow the users to group collapse or expand rows elsewhere on the page. Thanks for your help...and sorry if there is a previous post. I could nto find anything via the search.

 

[Harvey12]

Hi Vbosoco23,

I had a similar issue not too long ago when I was creating a file for my manager, she needed the exact same thing for the rows to be able to expand and collapse, rest assured this is possible, but I only found it be possible by macro is this something that would be helpful?

Many thanks
Harvey

 

[vbsoco26]

Absolutely! I'm not the best with Macro, but I can navigate the code if given instruction. Would love to hear your suggestion. Thanks!

 

[Parrow]

if you know the password then just write a macro that unprotects the sheet, does what you want it to do, then protects it again.

sub do_stuff ()

sheetx.unprotect "password"
do your stuff
sheetx.protect "password"

end sub


I would put something where it says "do stuff" but I don't know exactly what it is you are wanting to do, so I figured you can figure that part out, if now reply with what you are exactly doing.

are you talking about a pivot table or just hiding columns and rows, I guess that is where my confusion on your intent is.

 

[vbsoco26]

Actually, we have several rows grouped to be able to collapse/Expand with the plus/minus sign. Once we protect the page, we can no longer utilize this functionality.
The protected cells are always shown, and it is up to the users to decide if they want to collapse the rows or not. So, basically I was looking for "something" that would allow the page to be protected, and the user to be able to click the plus/minus sign to hide or unhide at will. I dont know if that explained it any better or not.

ummm...

Rows 38-40 are grouped
When I protect the sheet, I can no longer collapse the rows.
I need to be able to utilize the collapse/expand functionality with the protection on at will.
The locked cells have no barring on the grouped rows.

is that better?

 

[Parrow]

edit:

nevermind. wrong answer to wrong spot. lol. sorry


To the best of my knowlege, you will have to unlock the sheet to use the group function. Maybe some of the smarter folks have a different answer, but I am not aware of a way to do it without unlocking the sheet.

 

[Harvey12]

Hi Vbsoco26,

Try this:

Option Explicit

Sub Workbook_open()
    Dim ws As Worksheet
    For Each ws In Sheets
        With ws
            .Unprotect Password:="PASSWORD"
            .Protect Password:="PASSWORD", UserInterfaceOnly:=True
            .EnableOutlining = True
        End With
    Next ws
End Sub

Sub UnProtectAll()
    Dim wSheet                As Worksheet
    Dim Pwd                   As String
    Pwd = InputBox("Enter your password to unprotect all worksheets", "Password")
    On Error Resume Next
    For Each wSheet In Worksheets
        wSheet.Unprotect Password:=Pwd
    Next wSheet
    If Err <> 0 Then
        MsgBox "You have entered an incorrect password. All worksheets could not " & _
               "be unprotected.", vbCritical, "Incorrect Password"
    End If
    On Error GoTo 0
End Sub

Where 'PASSWORD' is written in caps, this is where you can type a desired password, when the open.workbook macro runs, this is an automatic macro that runs when the file is opened but just to be sure, run it before you close the file and then save it, this will protect all of your sheets at once and then hopefully still allow the grouping methods.

The second macro is for yourself this will unprotect all of the sheets when the correct password is typed in.

Also to note, you will not be able to have any of the cells inside the groups formatted as 'locked'. Attempt this initially with the exact file set up, if you get any issues let me know!