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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
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

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

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
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).
 

bluepenink

Well-known Member
Joined
Dec 21, 2010
Messages
585

ADVERTISEMENT

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:

bluepenink

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

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
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:

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,252
Office Version
  1. 2016
Platform
  1. Windows
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:

Forum statistics

Threads
1,136,260
Messages
5,674,681
Members
419,520
Latest member
Jennifer4Dillon

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
Top