Grouping in a protected Sheet Problem

[Solly]

Hi,

I am using 210 excel. I have created groups in two sheets, sheet 1 and 3. I am having a problem using the grouping in a protected sheet. In addition I have also downloaded and installed a date picker for excel. There are now quite a lot of code in VBA. I do not know anything about VBA so please be specific when you telling me what to do and where to put the new code you wil suggest.

Thanks

 

[lundbhaiz]

use this:

Private Sub Workbook_Open()

Dim wSheet As Worksheet
    For Each wSheet In Worksheets
        
        wSheet.EnableOutlining = True
        Next wSheet

End Sub

in excel press Alt+F11, in vb editor, find your file on left top, Right click "This Workbook", Insert, Module and paste.

 

[Solly]

Hi,


I have opened VBA, right clicked on workbook, inserted module and pasted as you suggested. I then closed the workbook to save. When I re opened it the grouping would still not work. When I put the code the work book was still protected. Does that make any difference. Where did I go wrong?

 

[RoryA]

You need to protect using userinterfaceonly:=true

Private Sub Workbook_Open()

Dim wSheet As Worksheet
    For Each wSheet In Worksheets
        wsheet.protect password:="your password", userinterfaceonly:=true
        wSheet.EnableOutlining = True
        Next wSheet

End Sub

 

[Solly]

Hi Rory,

Thanks for the reply. Please forgive my ingnorance but userinterfaceonly:=true is already in the code.

I have inserted the code in a new module but it still not working. Is there any steps I am missing? I already have 3 other modules for a date picker. will this new code cause any clash? What is your suggestions. Please remember I know nothing about VBA so please be speciffic.

Thanks
Suleiman

 

[RoryA]

Where is it in the code? I can't see it.

That code needs to go into the ThisWorkbook module.

 

[Solly]

Hi Rory,

Please see below:

Private Sub Workbook_Open()

Dim wSheet As Worksheet
For Each wSheet In Worksheets
wsheet.protect password:="your password", userinterfaceonly:=true
wSheet.EnableOutlining = True
Next wSheet

End Sub

 

[RoryA]

but that's the code I just posted.

 

[Solly]

Hi Rory,

As you can see below I have put in the code as you gave but its not working.

Private Sub Workbook_Open()
Dim wSheet As Worksheet
For Each wSheet In Worksheets
wSheet.Protect Password:="d3ykb1", userinterfaceonly:=True
wSheet.EnableOutlining = True
Next wSheet
End Sub

I have inserted a 4th module and pasted the code in there. I then closed the workbook to save changes and re oponed it but still not working.

 

[RoryA]

Again, that code needs to go into the ThisWorkbook module. It will not work anywhere else.