dynamic protecting/unprotecting

[sassriverrat]

Good Morning,

I have a workbook that initially has four sheets, two of which are very hidden. However, almost every time the workbook is opened, a new sheet is going to be added, which will be named either "noon"/"noon#"/"Arrival" (at the end). The number of "Noons" is dynamic, sometimes there's 15 Noons (Noon, Noon2, Noon3, etc) and sometimes none. There is always one "Arrival" at the very end. The Noons and Arrival are generated and formatted via vba. I'd like to protect the sheets so the user can't delete anything- I figure this is easy as I could just add the code into the end of the macro that creates the sheet. However, I'd like to have a button on one of the hidden sheets (named "Developer") that unlocks all of the protected sheets. If the number of sheets is changing, how would I do this? My limited experience for this macro would be to record myself protecting a sheet and then cleaning up the recorded macro- but that's only good for the sheets created and not those that are not....

Thanks!

 

[Kamolga]

Hi,

To unprotect all sheets:

Dim sh as worksheet
For each sh in activeworkbook.worksheets
sh.unprotect
Next sh

When in VBA, click on Thisworkbook below the sheets in left column and paste

Private Sub Workbook_Open()
Dim sh As Worksheet


For Each sh In ActiveWorkbook.Worksheets
    sh.Protect Password:="[COLOR=#0000ff]YourPassword[/COLOR]", UserInterFaceOnly:=True
Next sh
End Sub

to protect all sheets when opening the workbook. Note that userinterfaceonly makes it protected for users but not the macro (so you don't need protect/unptotect sheets in your macros).

If you are like me and often forget to protect/hide sheets after usage for the next user, you might appreciate a macro doing it for you on workbook opening by adding hiding status to the previous macro:

Private Sub Workbook_Open()
Dim sh As Worksheet
    For Each sh In ActiveWorkbook.Worksheets
        sh.Protect Password:="[COLOR=#0000ff]YourPassword[/COLOR]", UserInterFaceOnly:=True
        If sh.Name = "Developer" Then
            sh.Visible = xlSheetHidden
            Else
            sh.Visible = xlSheetVisible
        End If
        If sh.Name = "[COLOR=#0000ff]veryhiddensheet1[/COLOR]" Or sh.Name = "[COLOR=#0000ff]verhiddensheet2[/COLOR]" Then
            sh.Visible = xlSheetVeryHidden
        End If
    Next sh
End Sub

so you can unhide and unprotect all, work and save, the next user will always have the right protected and hidden sheets

 

[sassriverrat]

I like the way you think!

So based on what you said, I put this to the button

Sub UnProtector()Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
sh.Unprotect
Next sh
End Sub

And then I added your last piece of code there. I realize it's kind of redundant because, like you, I just had this piece in there for the same idea!

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'This is the last event to run as Excel is closing


Sheets("Notes").Visible = xlVeryHidden
Sheets("Developer").Visible = xlVeryHidden




End Sub

Thank you!