Your 'Sheet1 Active' workbook works fine for me - immediately it is opened it switches from Sheet3 to Sheet1. Same behaviour for both Excel 2010 and Excel 2003.
Your limitdate workbook's macro falls over because you haven't declared the 'limitdate' variable. Furthermore, you don't need the 'Application.EnableEvents = True' code as it does nothing in the context in which you have it.
You have numerous comments in your files about what you're trying to achieve. I don't propose to do your development work for you. However, you might find the following code, which I've posted elsewhere, useful.
The following code works by automatically hiding all except a 'main' worksheet upon opening and closing. It then asks the user for their worksheet name and password. If the correct password for the named sheet is given, it is displayed and unprotected; otherwise the prompt is repeated. If the prompts are left empty, the code does nothing more and exits.
The code is also written around the principle that the workbook itself should be protected - see the relevant comments in the code. This is to protect its structure etc, so that you won't run into problems with sheets being renamed etc.
You may want to add more code to trap 'Save' events, in case the user saves the workbook with their worksheet visible and a subsequent user opens the workbook with macros disabled.
Code:
Option Explicit
Dim User As String, UPwd As String, WPwd As String
Dim Err As Boolean, wsSheet As Worksheet, wsActvSht As Worksheet
Private Sub Workbook_Open()
WPwd = "" 'Inset the Workbook Password between the double quotes
Set wsActvSht = ThisWorkbook.Sheets("Sheet1") ' A worksheet that must remain visible
If ThisWorkbook.ProtectStructure = True Or ThisWorkbook.ProtectWindows = True Then ThisWorkbook.Unprotect WPwd
wsActvSht.Activate
For Each wsSheet In ActiveWorkbook.Worksheets
With wsSheet
If .Name = wsActvSht.Name Then
.Visible = xlSheetVisible
Else
.Visible = xlSheetVeryHidden
End If
End With
Next wsSheet
Restart:
User = InputBox("Please Input your Worksheet Username")
UPwd = InputBox("Please Input your Worksheet Password")
For Each wsSheet In ActiveWorkbook.Worksheets
With wsSheet
If .Name = User Then
On Error GoTo Restart
If .ProtectContents = True Then .Unprotect UPwd
.Visible = xlSheetVisible
.Activate
Exit Sub
End If
End With
Next wsSheet
ThisWorkbook.Protect Password:=WPwd, Structure:=True, Windows:=True
End Sub
Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ThisWorkbook.ProtectStructure = True Or ThisWorkbook.ProtectWindows = True Then ThisWorkbook.Unprotect WPwd
For Each wsSheet In ActiveWorkbook.Worksheets
With wsSheet
If .Name = User Then .Protect UPwd
If .Name <> wsActvSht.Name Then .Visible = xlSheetVeryHidden
End With
Next wsSheet
wsActvSht.Activate
ThisWorkbook.Protect Password:=WPwd, Structure:=True, Windows:=True
End Sub