UserForm That Brings Specific Worksheet Up, Hides All Else

[KittenSwagger]

Hello,

Im trying to make something that will bring up one specific worksheet when a username and password is entered, and only that sheet. I am running into problems where It opens the last saved worksheet can be seen in the background until you enter the username/password. Additionally, it will only show the correct worksheet after you click on the tabs, then runs a "You cant do that" message for every other tab.

I want to make this as simple as a I can so that it simply bring us ONE worksheet automatically when they enter their username/password. can anyone help me out?

This is the code in the Workbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim w As Worksheet
    Dim bSaveIt As Boolean

    bSaveIt = False
    For Each w In Worksheets
        If w.Visible Then
            Select Case w.Name
                Case "akduen1"
                    w.Protect ("akduen1")
                    w.Visible = False
                    bSaveIt = True
                Case "alkewle"
                    w.Protect ("alkewle")
                    w.Visible = False
                    bSaveIt = True
                Case "jrriepe"
                    w.Protect ("jrriepe")
                    w.Visible = False
                    bSaveIt = True
                Case "ktwoode"
                    w.Protect ("ktwoode")
                    w.Visible = False
                    bSaveIt = True
                Case "mastr01"
                    w.Protect ("mastr01")
                    w.Visible = False
                    bSaveIt = True
                Case "mrrud"
                    w.Protect ("mrrud")
                    w.Visible = False
                    bSaveIt = True
                Case "wlgood"
                    w.Protect ("wlgood")
                    w.Visible = False
                    bSaveIt = True
                Case "manager"
                If txtPass.Text <> "coffee" Then bError = True
            Application.EnableEvents = False
            End Select
        End If
    Next w
    If bSaveIt Then
        ActiveWorkbook.CustomDocumentProperties("auth").Delete
        ActiveWorkbook.Save
    End If
End Sub

Private Sub Workbook_Open()
    UserForm1.Show
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If Sh.Name <> "Main" Then
        If Sh.Name <> ActiveWorkbook.CustomDocumentProperties("auth").Value Then
            Sh.Visible = False
            MsgBox "You Can't Do That, Sorry."
        End If
    End If
End Sub

This is the code in the User Form:

Private Sub btnOK_Click()
    Dim bError As Boolean
    Dim sSName As String
    Dim p As DocumentProperty
    Dim bSetIt As Boolean

    bOK2Use = False
    bError = True
    If Len(txtUser.Text) > 0 And Len(txtPass.Text) > 0 Then
        bError = False
        Select Case txtUser.Text
            Case "akduen1"
                sSName = "akduen1"
                If txtPass.Text <> "akduen1" Then bError = True
             Case "alkewle"
                sSName = "alkewle"
                If txtPass.Text <> "alkewle" Then bError = True
            Case "jrriepe"
                sSName = "jrriepe"
                If txtPass.Text <> "jrriepe" Then bError = True
             Case "ktwoode"
                sSName = "ktwoode"
                If txtPass.Text <> "ktwoode" Then bError = True
            Case "mastr01"
                sSName = "mastr01"
                If txtPass.Text <> "mastr01" Then bError = True
             Case "mrrud"
                sSName = "mrrud"
                If txtPass.Text <> "mrrud" Then bError = True
            Case "wlgood"
                sSName = "wlgood"
                If txtPass.Text <> "wlgood" Then bError = True
            Case "admin"
                If txtPass.Text <> "coffee" Then bError = True
            Application.EnableEvents = False
         End Select
    End If
    If bError Then
        MsgBox "Invalid UserID or Password"
    Else
        
                
        bOK2Use = True
        Unload UserForm1
End If
End Sub
Private Sub UserForm_Terminate()
    If Not bOK2Use Then
        ActiveWorkbook.Close (False)
    End If
End Sub

[Norie]

Any chance you could upload a sample workbook?

Click on GO ADVANCED, scroll down and click Manage Attachments.