Excel Forum

Code: Select all

Dim bOK2Use As Boolean

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 "user1"
                sSName = "u1sheet"
                If txtpass.Text <> "u1pass" Then bError = True
            Case "user2"
                sSName = "u2sheet"
                If txtpass.Text <> "u2pass" Then bError = True
            Case Else
                bError = True
        End Select
    End If
    If bError Then
        MsgBox "Invalid User Name or Password"
    Else
        'Set document property
        bSetIt = False
        For Each p In ActiveWorkbook.CustomDocumentProperties
            If p.Name = "auth" Then
                p.Value = sSName
                bSetIt = True
                Exit For
            End If
        Next p
        If Not bSetIt Then
            ActiveWorkbook.CustomDocumentProperties.Add _
              Name:="auth", LinkToContent:=False, _
              Type:=msoPropertyTypeString, Value:=sSName
        End If

        Sheets(sSName).Visible = True
        Sheets(sSName).Unprotect (txtpass.Text)
        Sheets(sSName).Activate

        bOK2Use = True
        Unload UserForm1
    End If
End Sub
Private Sub UserForm_Terminate()
    If Not bOK2Use Then
        ActiveWorkbook.Close (False)
    End If
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim w As Worksheet
    Dim bSaveIt As Boolean
    Dim Nm As Names
    
    bSaveIt = False
    For Each Nm In Worksheets
        If w.Visible Then
            Select Case Nm.textuser.Text
                Case "user1"
                    Sheet2.Visible = True
                    w.Protect ("u1pass")
                    w.Visible = False
                    bSaveIt = True
                Case "user2"
                    Sheet3.Visible = True
                    w.Protect ("u2pass")
                    w.Visible = False
                    bSaveIt = True
                    

            End Select
        End If
    Next Nm
    If bSaveIt Then
        ActiveWorkbook.CustomDocumentProperties("auth").Delete
        ActiveWorkbook.Save
    End If
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 don't have authorization to view that sheet!"
        End If
    End If
End Sub