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. 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 |
Password protect not working
Hello all, Help onb this please..... -- 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 |
Password protect not working
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" wrote in message ... 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 |
All times are GMT +1. The time now is 11:20 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com