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

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

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

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

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

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

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

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

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

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