VBA to Protect Worksheet -- Strange Behavior

bisel

Board Regular
Joined
Jan 4, 2010
Messages
153
Greetings all,

Something very strange happening that I am hoping someone can shed light on ...

I have a private sub in the worksheet module. At the start of the sub I test to see if the worksheet is protected or unprotected. If protected, I set a boolean variable to TRUE. I then unprotect the sheet to perform some actions and then at the end of the sub I simple exit if the the worksheet was initially unprotected or I protect the sheet if it was originally protected.

VBA Code:
Private Sub Worksheet_Activate()
Dim reprotect As Boolean
Dim c As Range
Dim i As Integer

‘ Check is the sheet is protected. If it is, set reprotect variable to TRUE
If Sheet1.ProtectContents = True Then
    Sheet1.Unprotect
    reprotect = True
Else
    reprotect = False
End If

….
Do stuff here while sheet is unprotected
….

‘ Reprotect the sheet if originally protected
If reprotect = True Then
    Sheet1.Protect DrawingObjects:=True, Contents:=True, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
    Sheet1.EnableSelection = xlUnlockedCells
Else
End If

End Sub

When I reprotect the sheet, I use the options to allow the user to use filtering and to sort a table. However, when the event code runs upon sheet activation, the resulting level of protection is only allow selection of the unlocked cells. The user is not allowed to use the filter / sort functions on the table.

The strange part is that if I place the sheet protection statement outside of the If-Then-Else statement, then the appropriate level of protection is applied and the user can then perform filters and sorts.

Anyone have any idea what might be going on? Any workaround?

Thanks,

Steve
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
do you need the second else, you are only doing the reprotect if it tests true

you could write that as

VBA Code:
If reprotect = True Then Sheet1.Protect DrawingObjects:=True, Contents:=True, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True: Sheet1.EnableSelection = xlUnlockedCells
 

bisel

Board Regular
Joined
Jan 4, 2010
Messages
153
Never mind. I found the problem.

The issue has nothing to do with the IF THEN ELSE statement. I also have another event triggered sub which runs whenever the user selects a cell in the table. That event also had a routine to unprotect the sheet and then reprotect it after performing some operations. I had omitted to change the reprotection statement in that sub. So the protection method was fine for sheet activation but was flawed when a user selected a cell in the table.

All fixed now.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,976
If you set UserInterfaceOnly to True, you don't have to worry about un/re-protecting the sheets.
 

peterinca

New Member
Joined
Jan 13, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

@mikerickson ,

I'm also having trouble with a spreadsheet where I need to keep sheets protected for end-users but permit changes to be made, through VBA, to those sheets. Based on this thread (I'm a novice!), I inserted the following code to one of the sheets in the spreadsheet:


Private Sub Worksheet_Activate()

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'-----Unprotect Sheets.
Worksheets("Setup").Unprotect "password"
Worksheets("Cost").Unprotect "password"
Worksheets("Use").Unprotect "password"

--+
Do stuff
--+
'-----Reprotect the sheets.

Worksheets("Setup").Protect "password"
Worksheets("Cost").Protect "password"
Worksheets("Use").Protect "password"


End Sub

I'm now able to "do stuff" on the active sheet (Setup). However there are 2 modules and a number of other sheets with macros that reference protected sheets.

Where would I place the command (or change setting) "UserInterfaceOnly to True" you recommended? Is there a global spreadsheet setting that I can make (and then delete the programming above in the Setup sheet?

Thank you in advance for your help.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,976
UserInterFaceOnly is an argument of the Protect method. The syntax would be
VBA Code:
Worksheets("Setup").Protect "password", UserInterFaceOnly:= True
 

peterinca

New Member
Joined
Jan 13, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Thank you for the quick reply @mikerickson

Have added this code to the workbook open area. Appears to work so now will test the rest of the model.
 

peterinca

New Member
Joined
Jan 13, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Everything worked fine until I protected the workbook (there are tabs that the end user shouldn't see).

There is a tab ("Contents") that is always one tab behind the active worksheet. Because the workbook is now protected, I get the following error:
"Move method of Worksheet class failed".

What code would I use and where would I place so that Workbook protection is bypassed?

VBA Code:
Private Sub Workbook_Activate()

End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'Update by Extendoffice
Application.EnableEvents = False
Application.ScreenUpdating = False
If Application.ActiveSheet.Index <> Application.Sheets("CONTENTS").Index Then
    Application.Sheets("CONTENTS").Move Before:=Application.Sheets(Application.ActiveSheet.Index)
    Application.Sheets("CONTENTS").Activate
    Sh.Activate
End If
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

I tried placing this code, however it didn't work:

VBA Code:
Private Sub Workbook_Open()
        Sheets("Disclaimer").Activate
        ActiveWorkbook.Unprotect "Passwordd", UserInterfaceOnly:=True
        Worksheets("Setup").Protect "Password", UserInterfaceOnly:=True
        Worksheets("Cost").Protect "Passowrd", UserInterfaceOnly:=True
        Worksheets("Use").Protect "Password", UserInterfaceOnly:=True
        Worksheets("CONTENTS").Protect "Password", UserInterfaceOnly:=True
End Sub

Thx again for your help.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,127
Messages
5,628,860
Members
416,345
Latest member
sayad

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