PDA

View Full Version : Solved: Lock sheet (non-viewable) error



Adonaioc
09-03-2008, 08:17 AM
I have it almost the way i want it, i got this code off a site and modified it a little, but im having trouble with a runtime error "object variable or with block variable not set.

It mainly only happens when the workbook is saved on sheet one, closed, reopened, sheet 2 is selected, and the password prompt is "X" 'd out of.

Wouldnt really be a problem but when it errors out it leaves you on the "protected" sheet and all you have to do is unhide.

Any help i can get would be nice, im at my wits end.:think:



Dim sLast As Object

Private Sub Workbook_Open()
'Ensure Sheet1 is not the active sheet upon opening.
If Sheet2.Name = ActiveSheet.Name Then Sheet1.Select
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim strPass As String
Dim lCount As Long

If Sh.CodeName <> "Sheet2" 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
Sheet2.Columns.Hidden = True
'Allow 3 attempts at password
For lCount = 1 To 3
strPass = InputBox(Prompt:="Please Enter the Password", Title:="Password")
If strPass = vbNullString Then 'Cancelled
sLast.Select
Exit Sub
ElseIf strPass <> "Barr" Then 'InCorrect password
MsgBox "Password incorrect", vbCritical, "Password Incorrect"
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

Adonaioc
09-03-2008, 11:53 AM
I hope that my problem is clear, if not let me know. Thanks in advance

Adonaioc
09-04-2008, 07:04 AM
Is anyone out there?

Adonaioc
09-04-2008, 07:15 AM
Here is the file passwords are "Password" hope that helps

Adonaioc
09-04-2008, 07:17 AM
oh i almost forgot the password for the vba project is "password" the password for sheet 2 is "Password" cap sensitive

Bob Phillips
09-04-2008, 08:49 AM
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim strPass As String
Dim lCount As Long

If Sh.CodeName <> "Sheet2" 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
Sheet2.Columns.Hidden = True
'Allow 3 attempts at password
For lCount = 1 To 3

strPass = InputBox(Prompt:="Please Enter the Password", Title:="Password")
If strPass = vbNullString Then 'Cancelled

Sheet1.Select
Exit Sub
ElseIf strPass <> "Password" Then 'InCorrect password

MsgBox "Password incorrect", vbCritical, "Password Incorrect"
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

Adonaioc
09-04-2008, 10:04 AM
Perfect! thank you so much, changing it from sLast to sheet1 worked perfectly.

Thanks again.