VBA to Protect Worksheet -- Strange Behavior

[bisel]

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.

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

 

[mole999]

do you need the second else, you are only doing the reprotect if it tests true

you could write that as

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

 

[bisel]

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]

If you set UserInterfaceOnly to True, you don't have to worry about un/re-protecting the sheets.

 

[peterinca]

@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]

UserInterFaceOnly is an argument of the Protect method. The syntax would be

Worksheets("Setup").Protect "password", UserInterFaceOnly:= True

 

[peterinca]

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]

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?

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:

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.