<TABLE border=0 cellSpacing=0 cellPadding=2 width="100%"><TBODY><TR><TD align=left></TD></TR></TBODY></TABLE><TABLE border=0 cellSpacing=0 cellPadding=2 width="100%"><TBODY><TR><TD align=left></TD></TR></TBODY></TABLE><TABLE border=0 cellSpacing=0 cellPadding=2 width="100%"><TBODY><TR><TD align=left></TD></TR></TBODY></TABLE>I am trying to use the following code. It does not work for me as is. My sheet name is still 'Sheet1'.
I need to tweek it to disable viewing of all sheets. When user clicks on their sheet/tab, I want the password box to pop up. If the password is correct, I want the sheet to be editable during the session. Thanks!
The following was pulled from another source:
The Workbook_Open procedure is there to ensure the Workbook does not open with the un-viewable Worksheet being active.
To use this code: While in Excel proper, right click on the Excel icon, top left next to File and choose View Code it is in here you must paste the code below and change to suit your needs.
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
I need to tweek it to disable viewing of all sheets. When user clicks on their sheet/tab, I want the password box to pop up. If the password is correct, I want the sheet to be editable during the session. Thanks!
The following was pulled from another source:
The Workbook_Open procedure is there to ensure the Workbook does not open with the un-viewable Worksheet being active.
To use this code: While in Excel proper, right click on the Excel icon, top left next to File and choose View Code it is in here you must paste the code below and change to suit your needs.
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
Last edited:
