protect/unprotect workbook macro

bluepenink

Well-known Member
Joined
Dec 21, 2010
Messages
585
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?

Code:
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
 
Last edited by a moderator:

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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).
 
Upvote 0
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.
 
Upvote 0
Ok that's what I thought. For example:

Rich (BB code):
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).
 
Upvote 0
Ok that's what I thought. For example:

Rich (BB code):
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

Rich (BB code):
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
 
Last edited:
Upvote 0
strangely....i saved and opened the file again..and it still messes up on the "grouped/ungrouped column"

Code:
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?
 
Last edited:
Upvote 0
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.
 
Last edited:
Upvote 0
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:

Code:
Private Sub Workbook_Open()
    With Sheet1
           .Protect Password:="password", UserInterfaceOnly:=True
           .EnableOutlining = True
    End With
End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top