protected tab

[lucian688]

Hello - I have a workbook including some 62 tabs - one is summary report and 61 tabs for different regions. I protected each 61 tab in order to allow only each region to access their own tab. I also created a master password for the workbook.

My question is - every month, I have to modify the file, do I need to unprotect each tab and protect again every time?

Any help is greatly appreciated!!

Thanks,

Mary

 

[hallingh]

Yes. But you can use a simple macro to do it for you all at once:

Sub ProtectIt()
Dim wSheet As Worksheet
    For Each wSheet In Worksheets
    wSheet.Protect Password:="password"
    Next wSheet
 
End Sub
Sub UnprotectIt()
Dim wSheet As Worksheet
    For Each wSheet In Worksheets
    wSheet.Unprotect Password:="password"
    Next wSheet

End Sub

Put those subs in your module, and whenever you want to unprotect the sheets run the UnprotectIt macro and when you want to protect them again run the ProtectIt macro.

Hope this helps.

Hank

 

[lucian688]

Oh, that is right! Thanks so much for your help!

 

[lucian688]

Hi Hank, something changed - my boss want to hide each tab. Only region A can open and see their own tab with their own password.

I am thinking about creat a master file in the front: A, B, C, D....by clicking A, a request of password will pop up. If A region manager put in the right password, it will lead him only to his own tab.

How am I going to do this...

Many thanks,

Mary

 

[hallingh]

Hmmmm not sure how to do that efficiently with that many tabs. Here is code that will password protect a sheet via a portal button or something of the sort:

Sub PasswordProtectSheet_Click()
Dim Password As String
Dim Reply As String
String = Sheets("Passwords").Range("A1").Value
Reply = InputBox("Please enter the password to go to your sheet", "Enter Password")
If Len(Reply) = 0 Then Exit Sub
If Not Reply = Password Then
  MsgBox "You have entered an invalid password.": Exit Sub
Else
  Sheets("MySheet").Activate
End If
End Sub

This is defining a variable, Password, as whatever is kept in cell A1 of the sheet called "Passwords".

I'm sure there is a much better way to do this, just figured I'd write this quickly to get you started. I'm leaving work for the day, but will be back on tomorrow and will help you if no one else hsa responed.

Hope this helps.

Hank

 

[lucian688]

Okay - Thanks so much!

 

[lucian688]

I was thinking if I can hyperlink A in master file to tab A, but tab A is password protected and hidden. Do I still have to use code....

Region
A
B
C
D

Thanks,

Mary