| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing 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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Preventing people from viewing specific worksheets
That would indicate that there is an error in your code.
-- Regards, Tom Ogilvy "k1ckn1ck" wrote in message oups.com... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Preventing people from viewing specific worksheets
i figured there was an error. I just dont know enough about this kind
of thing to find the error |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Preventing people from viewing specific worksheets
What line of code is highlighted when you get the error?
-- Regards, Tom Ogilvy "k1ckn1ck" wrote in message oups.com... i figured there was an error. I just dont know enough about this kind of thing to find the error |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Preventing people from viewing specific worksheets
This line
" Sheet1.Columns.Hidden = True " I am currently using office 2003 but this spreadsheet will mainly be used by people on office 2000 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Preventing people from viewing specific worksheets
the code worked for me.
Do you have a sheet with a codename of Sheet1? Is the code in the ThisWorkbook Module? -- Regards, Tom Ogilvy "k1ckn1ck" wrote in message ups.com... This line " Sheet1.Columns.Hidden = True " I am currently using office 2003 but this spreadsheet will mainly be used by people on office 2000 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Preventing people from viewing specific worksheets
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Preventing people from viewing specific worksheets
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Preventing people from viewing specific worksheets
It doesn't use excel's password capabilities. It hard codes a password in
the code itself: strPass < "Secret" -- Regards, Tom Ogilvy "k1ckn1ck" wrote in message oups.com... 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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Preventing people from viewing specific worksheets
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. -- Regards, Tom Ogilvy "k1ckn1ck" wrote in message ups.com... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Preventing new worksheets when drilling down in PIVOTs | Excel Worksheet Functions | |||
| Don't allow certain people to view worksheets | Setting up and Configuration of Excel | |||
| Preventing Viewing of certain columns by recipient | Excel Discussion (Misc queries) | |||
| can you prevent viewing of specific columns in a worksheet | Excel Worksheet Functions | |||
| Preventing users from deleting worksheets | Excel Programming | |||
Linear Mode
