protect/unprotect workbook macro

[bluepenink]

Hi all

I have the following macro in a module to protect/unprotect workbook...it's working fine but i have a few columns "grouped" so when i try to "ungroup" them, it says, sheet is protected.

i have tried to define it in the macro so not sure what I am doing wrong.
I am using Excel 2013.

also, can it also be added on the macro to disable "hide/unhide" sheet feature?

Private Sub Unprotect_All_Sheets()
    Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        ws.Unprotect Password:="78749"
    Next ws
End Sub

Private Sub ProtectAllSheets()
'
' Protect all sheets in a workbook
' Macro recorded XXX
'
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
    ws.Protect Password:="78749", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingColumns:=True
    ws.EnableSelection = xlNoRestrictions
    Next ws


End Sub

 

[Jon von der Heyden]

Hi

Do you mean you are unable to expand / collapse? If so have a look at the Worksheet.EnableOutlining Property.

Unfortunately it does not persist between sessions, so you need to enable the property each time the workbook is opened (and for each relevant sheet).

 

[bluepenink]

Hi

Do you mean you are unable to expand / collapse? If so have a look at the Worksheet.EnableOutlining Property.

Unfortunately it does not persist between sessions, so you need to enable the property each time the workbook is opened (and for each relevant sheet).

i am just trying to make sure users can "+" group the columns or ungroup columns...that is the only feature that is being restricted.

thanks.

 

[Jon von der Heyden]

Ok that's what I thought. For example:

For Each ws In ThisWorkbook.Worksheets
    ws.EnableOutlining = True
    ws.Protect Password:="78749", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingColumns:=True
    ws.EnableSelection = xlNoRestrictions
Next ws

Unfortunately it does not persist between sessions, so you need to enable the property each time the workbook is opened (and for each relevant sheet).

 

[bluepenink]

Ok that's what I thought. For example:

For Each ws In ThisWorkbook.Worksheets
    ws.EnableOutlining = True
    ws.Protect Password:="78749", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingColumns:=True
    ws.EnableSelection = xlNoRestrictions
Next ws

yes its fine now...but the issue is now with my radio buttons/drop down menu

Private Sub Unprotect_All_Sheets()
    Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        ws.Unprotect Password:="78749"
    Next ws
End Sub


Private Sub ProtectAllSheets()
'
' Protect all sheets in a workbook
' Macro recorded xxx
'
    Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    ws.EnableOutlining = True
    ws.Protect Password:="78749", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingColumns:=True
    ws.EnableSelection = xlNoRestrictions
Next ws




End Sub

 

[bluepenink]

strangely....i saved and opened the file again..and it still messes up on the "grouped/ungrouped column"

Private Sub Unprotect_All_Sheets()
    Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        ws.Unprotect Password:="78749"
    Next ws
End Sub


Private Sub ProtectAllSheets()
'
' Protect all sheets in a workbook
' Macro recorded xx
'
    Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    ws.EnableOutlining = True
    ws.Protect Password:="78749", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingColumns:=True
    ws.EnableSelection = xlNoRestrictions
Next ws




End Sub

im not sure what im doing wrong at all?

 

[Jon von der Heyden]

strangely....i saved and opened the file again..and it still messes up on the "grouped/ungrouped column"

Unfortunately it does not persist between sessions, so you need to enable the property each time the workbook is opened (and for each relevant sheet).

I.e. you need to use the Workbook_Open event routine. You should loop through all of the sheets and enable outlining when the workbook is opened.

yes its fine now...but the issue is now with my radio buttons/drop down menu

Sorry, I don't know what you mean.

 

[Jaafar Tribak]

i am just trying to make sure users can "+" group the columns or ungroup columns...that is the only feature that is being restricted.

thanks.

Run this everytime the workbook is open

In the workbook module:

Private Sub Workbook_Open()
    With Sheet1
           .Protect Password:="password", UserInterfaceOnly:=True
           .EnableOutlining = True
    End With
End Sub