prevent select sheet if the password is wrong by inputbox

[abdelfattah]

hello I have about 10 sheets the first names is MAIN and the rests call( sh2,sh3... and so on )
so what I want when I select one of them to how the data it prevent select the sheet and return the sheet main and show inputbox to enter the password if is right then show what I select the sheet
note: if it's possible do that without hide and unhide the sheets

 

[Zot]

@Zot I don't want changing the main password I'M talking about if I select each sheet the password it 's different if I select sh2 the password should be "123 and if I select sh3 the password should be "111" and so on the rests of sheets

I believe that @Jaafar Tribak password is not for MAIN specifically but a master password to open any sheet. You did not mention that you need password for each sheet. Same like min, ask for password for any sheet except main but all password are the same.

 

[Zot]

I made modification to my code and remove unnecessary lines. I'm using dictionary to store password.

Here is the code if you are interested. Have to wait @Jaafar Tribak for his modification since I guess he is on later GMT than me

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Dim Pass As String
Dim PassOK As Boolean
Dim dictPwd As Object

Set dictPwd = CreateObject("Scripting.Dictionary")
' Set password here as sheetname, password
dictPwd.Add "Sheet2", "222"
dictPwd.Add "Sheet3", "333"
dictPwd.Add "Sheet4", "444"

If Not Sh.Name = "MAIN" Then
    If Not PassOK Then Sheets("MAIN").Select
Again:
    Pass = InputBox("Enter Password")
    If StrPtr(Pass) = 0 Then
        Sheets("MAIN").Activate
    ElseIf Pass = dictPwd(Sh.Name) Then
        PassOK = True
        MsgBox "Password Accepted"
        Application.EnableEvents = False
        Sh.Select
        Application.EnableEvents = True
        PassOK = False
    Else
        MsgBox "Wrong password"
        GoTo Again
    End If
End If

End Sub

 

[abdelfattah]

@Zot thanks but the code doesn't work well it show message wrong password even the password is right , where I make mistake?

 

[Jaafar Tribak]

@Zot thanks but the code doesn't work well it show message wrong password even the password is right , where I make mistake?

Based on Zot's last code :

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)

    Dim dictPwd As Object
    Dim oCurSheet As Worksheet
    Dim sPassword As String

   Set dictPwd = CreateObject("Scripting.Dictionary")

    With dictPwd
        .Add Sheet2, "222"
        .Add Sheet3, "333"
        .Add Sheet4, "444"
    End With
   
    On Error GoTo errHandler
   
    Application.EnableEvents = False
        Set oCurSheet = ActiveSheet
        Sheets("MAIN").Activate
        If Not oCurSheet Is Sheets("MAIN") Then
            sPassword = InputBox("Enter sheet password")
            If StrPtr(sPassword) = 0 Then GoTo errHandler
            If sPassword = dictPwd(oCurSheet) Then
                oCurSheet.Activate
            Else
                MsgBox "wrong password"
            End If
        End If
errHandler:
    Application.EnableEvents = True

End Sub

 

[Zot]

@Zot thanks but the code doesn't work well it show message wrong password even the password is right , where I make mistake?

I have tested it with no problem. So, I'm not sure at the moment. Just make sure you have sheet name set correctly. Use the better coding from @Jaafar Tribak

 

[abdelfattah]

@Jaafar Tribak many thanks for great answering

 

[abdelfattah]

@Zot thanks for your time