Selected Answer
Please paste the event procedure below into the ThisWorkbook code module.
Option Explicit
Private Sub Workbook_Open()
' 198
ThisWorkbook.Worksheets("TXL_5261").Activate
End Sub
In the workbook I created for testing I had a sheet called "TXL_5261". You can change the name to whatever suits you. The point is that it's a sheet that you are willing to show to all comers. Observe that this same sheet is also referred to in the code below. It's the one the user will be sent back to if access to the protected sheet is denied.
Paste the code below into the code sheet of the sheet that you want to protect.
Option Explicit
Private Sub Worksheet_Activate()
' 198
Dim TopLeft As Single ' Top Left of a blank cell
With ActiveSheet.UsedRange
TopLeft = Cells(1, .Column + .Columns.Count + 1).Left
End With
With ActiveWindow
.ScrollIntoView PointsToPixels(TopLeft), 0, _
PointsToPixels(.Width), _
PointsToPixels(.Height)
End With
If AllowAccess Then
ActiveWindow.ScrollIntoView 0, 0, 0, 0
Else
Worksheets("TXL_5261").Activate
End If
End Sub
Private Function AllowAccess() As Boolean
' 198
Const Pwd As String = "1234"
Dim MyPwd As Variant
Dim Fun As Boolean ' Function return value
Do
MyPwd = InputBox("Enter Password [blank to abort]", "DCA Input Screen")
If Len(MyPwd) = 0 Then Exit Do
Fun = CBool(StrComp(MyPwd, Pwd, vbBinaryCompare))
If Fun Then MsgBox "Invalid Password", vbCritical, "Access denied"
Fun = Not Fun
Loop Until Fun
AllowAccess = Fun
End Function
Private Function PointsToPixels(ByVal Pts As Single) As Long
'198
PointsToPixels = Pts * 96 / 72
End Function
Want to know how it works? When the user clicks on the tab of Sheet2 (the name is immaterial because the code knows which sheet from the location of the code) Excel will display that sheet come what may. My code doesn't fight Excel. Instead, it moves the sheet's used range out of sight while the user enters the password. He sees an unused, blank area of the sheet.
From there it's simple. If the password is correct the view is moved to the beginning of the sheet. If access is denied the default worksheet is called up.
Note that you can't test the function AllowAccess while it's in the worksheet's code module. If you move it to a standard code module you can test it there with F5, and if you remove the scope limitation "Pravate" from the declaration you can call it from the Immediate pane.
