Password protect not working

Hi all,

This is the code which I found used to protect a sheet from opening without a password. I have certain problems in this. First, I have renamed all the worksheets in the workbook which makes me difficult to identify which is sheet one as they are moved from positions. (I mean from the day when I opened this and when excels are named as sheet1,2,3)
So, this does not work. Also, I have given the name as "year-to-date" as a name to the sheet and I am not able to change the name in this code and I do not understand what to do.
The third problem is I am able to do this for a single sheet. What I need is the first 5 sheets should be protected and only the 6th sheet should be visible without a password. I am pasting this code in the 'this workbook code' in the left to file in the top left corner, if am right.
Can anyone please help me in setting this code right that I can have a password to all the 5 sheets? I need your help please. The code i tried is given below.

 
Dim sLast As Object 
 
Private Sub Workbook_Open() 
     'Ensure Sheet1 is not the active sheet upon opening.
    If Sheet1.Name = ActiveSheet.Name Then Sheet2.Select 
End Sub 
 
Private Sub Workbook_SheetActivate(ByVal Sh As Object) 
    Dim strPass As String 
    Dim lCount As Long 
     
    If Sh.CodeName <> "Sheet1" Then 
         'Set sLast variable to the last active sheet _
        This Is Then used To return the user To the _ 
        last sheet they were on If password Is Not known _ 
        Or they Cancel. 
        Set sLast = Sh 
         
    Else 
         'Hide Columns
        Sheet1.Columns.Hidden = True 
         'Allow 3 attempts at password
        For lCount = 1 To 3 
            strPass = InputBox(Prompt:="Password Please", Title:="PASSWORD REQUIRED") 
            If strPass = vbNullString Then 'Cancelled
                sLast.Select 
                Exit Sub 
            ElseIf strPass <> "Secret" Then 'InCorrect password
                MsgBox "Password incorrect", vbCritical, "Ozgrid.com" 
            Else 'Correct Password
                Exit For 
            End If 
        Next lCount 
         
        If lCount = 4 Then 'They use up their 3 attempts
            sLast.Select 
            Exit Sub 
        Else 'Allow viewing
            Sheet1.Columns.Hidden = False 
        End If 
    End If 
End Sub

Hello all,

Help onb this please.....

i'm not quite sure what you are looking for
but try this and see whether this is for or not.

Private Sub Workbook_SheetActivate(ByVal sh As Object)
Dim wsdf As Worksheet
Dim wdpass As String
Dim strpass
Dim co As Long

Application.EnableEvents = False
On Error GoTo ex

Set wsdf = Sheet6 '<<===change to the CodeName of the 6th sheet
wdpass = "secret" '<<===set passward here
Set sh = ActiveSheet
If Not sh Is wsdf Then
Do While co < 3
strpass = Application.InputBox(Prompt:="Password Please", Title:="PASSWORD
REQUIRED", Type:=3)
If strpass = False Then 'Cancelled
sh.Unprotect Password:=wdpass
sh.Columns.Hidden = True
sh.Protect Password:=wdpass
wsdf.Select
Exit Do
ElseIf strpass <> wdpass Then 'InCorrect password
MsgBox "Password incorrect", vbCritical, "Ozgrid.com"
sh.Unprotect Password:=wdpass
sh.Columns.Hidden = True
sh.Protect Password:=wdpass
Else 'Correct Password
sh.Unprotect Password:=wdpass
sh.Columns.Hidden = False
sh.Protect Password:=wdpas
Exit Do
End If
co = co + 1
Loop
End If

If co = 3 Then
wsdf.Select
End If

ex:
Application.EnableEvents = True
End Sub

keizi

"praveen_khm" <praveen_khm.230q9a_1139583003.9082@excelforum-nospam.com> wrote in
message news:praveen_khm.230q9a_1139583003.9082@excelforum-nospam.com...
>
> Hi all,
>
> This is the code which I found used to protect a sheet from opening
> without a password. I have certain problems in this. First, I have
> renamed all the worksheets in the workbook which makes me difficult to
> identify which is sheet one as they are moved from positions. (I mean
> from the day when I opened this and when excels are named as
> sheet1,2,3)
> So, this does not work. Also, I have given the name as "year-to-date"
> as a name to the sheet and I am not able to change the name in this
> code and I do not understand what to do.
> The third problem is I am able to do this for a single sheet. What I
> need is the first 5 sheets should be protected and only the 6th sheet
> should be visible without a password. I am pasting this code in the
> 'this workbook code' in the left to file in the top left corner, if am
> right.
> Can anyone please help me in setting this code right that I can have a
> password to all the 5 sheets? I need your help please. The code i tried
> is given below.
>
>
> Code:
> --------------------
>
>
> Dim sLast As Object
>
> Private Sub Workbook_Open()
> 'Ensure Sheet1 is not the active sheet upon opening.
> If Sheet1.Name = ActiveSheet.Name Then Sheet2.Select
> End Sub
>
> Private Sub Workbook_SheetActivate(ByVal Sh As Object)
> Dim strPass As String
> Dim lCount As Long
>
> If Sh.CodeName <> "Sheet1" Then
> 'Set sLast variable to the last active sheet _
> This Is Then used To return the user To the _
> last sheet they were on If password Is Not known _
> Or they Cancel.
> Set sLast = Sh
>
> Else
> 'Hide Columns
> Sheet1.Columns.Hidden = True
> 'Allow 3 attempts at password
> For lCount = 1 To 3
> strPass = InputBox(Prompt:="Password Please", Title:="PASSWORD REQUIRED")
> If strPass = vbNullString Then 'Cancelled
> sLast.Select
> Exit Sub
> ElseIf strPass <> "Secret" Then 'InCorrect password
> MsgBox "Password incorrect", vbCritical, "Ozgrid.com"
> Else 'Correct Password
> Exit For
> End If
> Next lCount
>
> If lCount = 4 Then 'They use up their 3 attempts
> sLast.Select
> Exit Sub
> Else 'Allow viewing
> Sheet1.Columns.Hidden = False
> End If
> End If
> End Sub
>
> --------------------
>
>
> --
> praveen_khm
> ------------------------------------------------------------------------
> praveen_khm's Profile:
http://www.excelforum.com/member.php...o&userid=30364
> View this thread: http://www.excelforum.com/showthread...hreadid=511050
>