VBA code to auto-protect worksheets on Open

GVK

New Member
Joined
Oct 13, 2022
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Hi, I am running the below code aiming to protect all worksheets while allowing for Group/Ungroup to work. The code works as intended but I would also like to be able to perform the below while the workbook/worksheet is password protected:

a. Allow filtering
b. Not allow 'Sort Smallest to Largest'
c. Not allow 'Sort Largest to Smallest'

Can anyone help with the change/s needed in the below code to also account for items a, b and c above?

Thank you very much in advance for your help.
------------------------------------------------------------------------------------------------------------------------------------
Sub ProtectAllWorksheets()
Dim ws As Worksheet
If ActiveWorkbook.Name = "[NAME]" Then
ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
ws.Protect Password:="[PASSWORD]", UserInterfaceOnly:=True
ws.EnableOutlining = True
Next ws
ScreenUpdating = True
Else
End If
End Sub
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
A follow-up - amending previous code to the below, indeed allows for Filtering while a sheet is password protected and does not allow sorting from the Data ribbon. Sorting is however still allowed from within the drop down filter and I want this NOT to be allowed either.

Is that possible?

Thank you.
---------------------------------------------------------------------------------------------------------------------------------------
Sub ProtectAllWorksheets()
Dim ws As Worksheet
If ActiveWorkbook.Name = "[NAME]" Then
ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
ws.Protect Password:="[PASSWORD]", UserInterfaceOnly:=True, AllowFiltering:=True, AllowSorting:=False
ws.EnableOutlining = True
Next ws
ScreenUpdating = True
Else
End If
End Sub
 
Upvote 0
Solution
Actually the latest code posted above solves the issue.
 
Upvote 0

Forum statistics

Threads
1,223,842
Messages
6,174,978
Members
452,596
Latest member
Anabaric

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