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
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