 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / January 2006Preventing people from viewing specific worksheets
Right i have a excel document with 54 worksheets. I want to password protect the first two worksheets with an identical password. Is it possible and how. I did find some code to do it 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 all it does is brings an error 1004 up That would indicate that there is an error in your code.  SignatureRegards, Tom Ogilvy > Right i have a excel document with 54 worksheets. > [quoted text clipped - 50 lines] > > End Sub all it does is brings an error 1004 up i figured there was an error. I just dont know enough about this kind of thing to find the error What line of code is highlighted when you get the error? SignatureRegards, Tom Ogilvy > i figured there was an error. I just dont know enough about this kind > of thing to find the error This line " Sheet1.Columns.Hidden = True " I am currently using office 2003 but this spreadsheet will mainly be used by people on office 2000 the code worked for me. Do you have a sheet with a codename of Sheet1? Is the code in the ThisWorkbook Module? SignatureRegards, Tom Ogilvy > This line > " [quoted text clipped - 3 lines] > I am currently using office 2003 but this spreadsheet will mainly be > used by people on office 2000 Ok i have got it to work but now i cannot see the find the password. What is the password inthe baove code and do i just change it in the code for it to change on the file right i now have it working. Dim sLast As Object Private Sub Workbook_Open() 'Ensure Sheet1 is not the active sheet upon opening. If Sheet1.Name = ActiveSheet.Name Then Sheet3.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 = Sh Else 'Hide Columns Sheet1.Columns.Hidden = True 'Allow 3 attempts at password For lCount = 1 To 3 strPass = InputBox(Prompt:="Insert Password", Title:="PASSWORD REQUIRED") If strPass = vbNullString Then 'Cancelled sLast.Select Exit Sub ElseIf strPass < dollars > "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 How would i edit this code to password protect sheet1 and sheet2 Many thanks Nick Dim sLast As Object Private Sub Workbook_Open() 'Ensure Sheet1 is not the active sheet upon opening. If Sheet1.Name = ActiveSheet.Name Then Sheet3.Select End Sub Private Sub Workbook_SheetActivate(ByVal Sh As Object) Dim strPass As String Dim lCount As Long If Sh.CodeName <> "Sheet1" and _ sh.CodeName <> "Sheet2" Then Set sLast = Sh Elseif sh.CodeName = "Sheet1" Then 'Hide Columns Sheet1.Columns.Hidden = True 'Allow 3 attempts at password For lCount = 1 To 3 strPass = InputBox( _ Prompt:="Insert Password", _ 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 Elseif sh.CodeName = "Sheet2" then 'Hide Columns Sheet2.Columns.Hidden = True 'Allow 3 attempts at password For lCount = 1 To 3 strPass = InputBox( _ Prompt:="Insert Password", _ 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 Sheet2.Columns.Hidden = False End If End If End Sub Untested, but if you say you have it working, then this modification should work. SignatureRegards, Tom Ogilvy > right i now have it working. > [quoted text clipped - 46 lines] > > Nick It doesn't use excel's password capabilities. It hard codes a password in the code itself: strPass <> "Secret" SignatureRegards, Tom Ogilvy > Ok i have got it to work but now i cannot see the find the password. > > What is the password inthe baove code and do i just change it in the > code for it to change on the file |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.