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]

Say that you created a UserForm asking for password. If the UserForm is not in Modeless mode, as long as the UserForm is displayed you cannot select any sheet. In fact you cannot do anything until the UserForm is unloaded. Once unloaded, then you can select any sheet you like. Is this what you wanted?

 

[abdelfattah]

Hi
I mean by inputbox user not userform I search in the internet but the problem all use by hide and inside the sheets I don't want that , I know how do that by the userform

 

[Zot]

Hi
I mean by inputbox user not userform I search in the internet but the problem all use by hide and inside the sheets I don't want that , I know how do that by the userform

Any reason not to use userform?

 

[abdelfattah]

actually no real reason but I prefer work inside the sheet not by userform creating form cause slow in my file but by inputbox is better
This is my view

 

[Jaafar Tribak]

Do you mean something like this ?

Code in the ThisWorkbook Module

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)

    Const PASSWORD = "1234"
    Dim sPassword As String
    Dim oCurSheet As Worksheet
   
    On Error GoTo errHandler
   
    Application.EnableEvents = False
        Set oCurSheet = ActiveSheet
        Sheets("MAIN").Activate
        sPassword = InputBox("Enter sheet password")
        If sPassword = PASSWORD Then
            oCurSheet.Activate
        Else
            MsgBox "wrong password"
        End If
errHandler:
    Application.EnableEvents = True

End Sub

EDIT: this assumes all worksheets share the same password. The code will need some tweaking if the sheets have a different password each.

 

[Zot]

actually no real reason but I prefer work inside the sheet not by userform creating form cause slow in my file but by inputbox is better
This is my view

I think InputBox is also behaving like modal userform. You cannot do anything if the InpuBox still displayed, right? This is an easy sample which I think that will perform what you wanted. This will ask password whenever you select different sheet other than MAIN

Code is in ThisWorkbook

Public PassOK As Boolean

Private Sub Workbook_Open()
PassOK = False
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

If Not Sh.Name = "MAIN" Then
    If Not PassOK Then Sheets("MAIN").Select
Again:
    ans = InputBox("Enter Password")
    If ans = "123" 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]

thanks guys two codes both works greatly !
just curiosity as @Jaafar Tribak The code will need some tweaking if the sheets have a different password each.
how is that, please?

 

[Zot]

thanks guys two codes both works greatly !
just curiosity as @Jaafar Tribak The code will need some tweaking if the sheets have a different password each.
how is that, please?

You just need to set password in this line
Const PASSWORD = "1234"

 

[abdelfattah]

@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