Modifying VBA to password protect numerous sheets within the same workbook

G’day Excel Forum

I'm having some trouble augmenting a bit of VBA code and I'm hoping someone can help me out

I would like to be able to add passwords to multiple sheets within a workbook.

I’ve found a code online that has allowed me to add a single password to a single sheet (the ‘database’ sheet in the attached example) however I’ve been unable to work out how to modify it to work for other sheets in the same workbook.

In the attached workbook the code can be found in the ‘ThisWorkbook’ excel object, ideally I’d like to add passwords to sheets T1, T2 and T3 sheets as well, however I can’t work out how to modify the code to do this.

Has anyone any idea how to do this?

Cheers for any advice

Regards

MattRNR

P.S. I've also been having some trouble putting attachments onto this forum so here's hoping there is actually an attachment on here (incidentally I was able to get the attachments to work while using excel general and didn't wan't to waste the forums spontaneous decision to work so appologies for not posting on the VBA specific page)

I've recently used this code. It will put password to all of the sheets in the workbook when you open the workbook.

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    	ws.Protect Password:=" Vesper"
Next ws
End Sub

G'day Igormigor

Appreciate this code mate, alas this doesn't quite solve the problem

This prevents modifications to the sheets; however I need the sheets to be invisible without entering a password as with the 'Vesper' password on the ‘database’ sheet of the attached example.

Effectively all I need to do is get that same code to work for the T1, T2 and T3 sheets but with a different password.

Sounds simple but can’t work it out for the life of me, any thoughts?

Appreciate the help

Regards
MattRNR

P.S. I appologize for the lateness of my response I've been out bush for a couple of days without internet

Hi MattRNR,
I have problem with suppressing the sheet not to be shown until the password is entered. Some conflict is appearing that is putting the code in the loop, hiding all sheets. :s
The best I can do is the code below. I know its not what you requested, but maybe can give you idea how to proceed.
Sorry could not help you.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Application.ScreenUpdating = False

NumNam = ActiveSheet.Name
Num = ActiveSheet.Index

If NumNam = "Search" Then
    For Each myWorksheet In Worksheets
        myWorksheet.Visible = True
    Next
End If

' The password for the diferent sheets are defined below
If NumNam = "Database" Then Pass = "Vesper" Else
If NumNam = "T1" Then Pass = "T1" Else
If NumNam = "T2" Then Pass = "T2" Else
If NumNam = "T3" Then Pass = "T3" Else

If NumNam <> "Search" Then
    If Application.InputBox("Enter Password", "Password") <> Pass Then
        MsgBox "Incorrect Password", vbCritical, "Error"
            Application.EnableEvents = False
' If the password is incorrect, it poens "Search" sheet
        Sheets("Search").Select
            Application.EnableEvents = True
    Else
    End If
End If
    
    Application.ScreenUpdating = True

End Sub

No worries mate, this has given me a couple of idea's I can run with.

appreciate the help mate

Regards

MattRNR