Conditional sheet protection in VBA (Row hide/unhide)

[SuperWamJarice]

Good Afternoon,

I am new on the forum and I didn't find the answer to my problem within the existing posts so I hope you will be able to help me.


I created an excel document that includes over 200 sheets. The purpose is to have inputs from the production team on each sheet. The document is protected so that they only have access to defined cells and that can't do any formating of any kind.
My front sheet sums up the content of the workbook. It contains the titles and subtitles with hyperlinks to reach the sheets the production team needs to fill in.
It contains, as well, buttons (with macros) to hide/unhide the subtitles to ease the search.
That is where my issue starts : I need to be able to use those "hide/unhide" buttons whether my workbook is protected or not. To be more specific:
- If the workbook is not protected, I just want it to remain unprotected even after the hide/unhide macro has been launched.
- If the workbook is protected, I need the macro to unprotect the sheet, hide or unhide the rows the macro refers to and then reprotect the sheet.

Here is one of the thousand codes I made to do that operation:

Sub groupePREF()

    If ActiveSheet.Protect(Content) = True Then
    ActiveSheet.UnProtect Mdp
    
        With Rows("4:74")
        If Not .Hidden Then .Hidden = True Else .Hidden = False
        End With


        Range("23:25,27:29,31:33,35:37,39:41,43:63,66:68,70:72").Select
        Selection.EntireRow.Hidden = True
        Range("A4").Select
     
     ActiveSheet.Protect Mdp
     
     Else
     
        With Rows("4:74")
        If Not .Hidden Then .Hidden = True Else .Hidden = False
        End With


        Range("23:25,27:29,31:33,35:37,39:41,43:63,66:68,70:72").Select
        Selection.EntireRow.Hidden = True
        Range("A4").Select
    
    End If


End Sub

and of course, it doesn't work!

If I just do unprotect at the begining of my macro to reprotect at the end it means that if my workbook is unprotected, as soon as I will use the macro, ok, it will hide or unhide the rows but I will en up with a protected sheet which is particularly annoying and time consuming when I am editing the document.

I hope this is clear enough...

Is anyone able to give me a hand on that issue?

Thank you very much in advance

Regards

PS: excuse my english, I am french !

 

[Kamolga]

I usually avoid this issue by protecting all sheets from users but not for the macros (userinterface in VBA) at the opening of the file (opening event is where you have all the pages listed in VBA, the last is normally thisworkbook; and following macro would do it with "Secret" as password)

Private Sub Workbook_Open()
Dim wSheetName As Worksheet
For Each wSheetName In Worksheets
wSheetName.Protect Password:="Secret", UserInterFaceOnly:=True
 Next wSheetname
 End Sub

You can set up different password for different sheets as well

The two good things are that

1) I do not care about sheet protections when writing macros (no need to unprotect at the begining and re protect after the operation, which is otherwise a nightmare when some "ifs" call deferent macros who also protect and unprotect

2) If I or someone else unprotect a sheet and forget to re-protect afterwards, the sheet is nevertheless protected for the next user.

I would then decide to lock and unlock cells and button based on the cases/users.

I speak perfectly French but avoided to answer in French so everyone would be able to understand my answer but I can translate any part if it helps

 

[SuperWamJarice]

Kamolga,

Thank you for your quick answer.
I think I might still have an issue with the hiding/unhiding of rows with that option because i would need anyway to insert some code to unprotect and then reprotect the sheet to be able to hide/unhide rows, wouldn't I?

 

[Kamolga]

If you want to hide/unhide manually, you would have to unprotect it manually of course;

if hide/unhide by macro, no the macro will perfectly hide/unhide the rows without unprotecting the sheet as long as the sheet has been protected through

sheet.protect userinterfaceonly:=true

(L'option userinterfaceonly:=True permet de modifier par VBA les cellules protégées)

If you do not like this method, you could indeed use a function that can say if the sheet is protected:

Function protection()
  Application.Volatile
  Set f = Sheets(Application.Caller.Parent.Name)
  protection = IIf(f.ProtectContents, "protégé", "Non protégé")
End Function
Function protection()
  Application.Volatile
  Set f = Sheets(Application.Caller.Parent.Name)
  protection = IIf(f.ProtectContents, "protégé", "Non protégé")
End Function

There is another method with a function (=formula) that will calculate if a sheet is protected:

Function protection()
  Application.Volatile
  Set f = Sheets(Application.Caller.Parent.Name)
  protection = IIf(f.ProtectContents, "protégé", "Non protégé")
End Function

So if now you type =protection() in a cell, it will say if the sheet is "protégé" or not. You can then use this value to now if the sheet was protected or not at the begining of the macro. But if the sheets are not userinterfaceonly, you will need a value change event on your sheets to recalculate as you could not type the formula through VBA in a locked cell..;and with 200 sheets, you will have speed issues / bug I believe

Example of second method in this workbook:

https://1drv.ms/x/s!AvmGsNl7aaaAgqpHi8LnS7QK9x1dWg

 

[SuperWamJarice]

Thank you very much for your time!!!
I will try that and revert as soon as I get it to work!

 

[SuperWamJarice]

Hi, Thank you for your time, thats great!