Secured workbook

[sastoka]

hi everyone,

I'm creating a workbook, for all the manger in my company. they have to have access to their department's sheet + the summary sheet.

So here is how I did.

when closed the workbook automatically set the visibility of every sheet to xlVeryHidden.
when open a user form is shown with a username and password that unhide the relevant sheet.
if the user try to close the userform it close the workbook.
The macro is protected by a password.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim w As Worksheet
    
    Sheets("How to Start").Visible = xlSheetVisible
    Sheets("Summary").Select
    ActiveSheet.Protect Password:="adminaveo"

    
    With Sheets("Main Data")
    
        If FilterMode = True Then Sheets("Summary").ShowAllData
        
    End With
    
    For Each w In Worksheets

        If w.Name <> ("How to Start") Then w.Visible = xlSheetVeryHidden
    
    Next w
    
    ActiveWorkbook.Save
    
End Sub


Private Sub Workbook_Open()

    Sheets("Summary").Visible = xlSheetVisible
    
    Sheets("How to Start").Visible = xlVeryHidden
    
    UserForm1.Show
    
End Sub

Dim bOK2Use As Boolean

Private Sub btnOK_Click()
    Dim bError As Boolean
    Dim sSName As String

    bOK2Use = False
    bError = True
    If Len(txtUser.Text) > 0 And Len(txtPass.Text) > 0 Then
        bError = False
        Select Case txtUser.Text
            Case "manager1"
                sSName = "HR"
                If txtPass.Text <> "human123" Then bError = True
            Case "manager2"
                sSName = "finance"
                If txtPass.Text <> "finace123" Then bError = True


            Case Else
                bError = True
        End Select
    End If
    If bError Then
        MsgBox "Invalid User Name or Password"
    Else
        Sheets(sSName).Visible = True
        Sheets(sSName).Activate

        bOK2Use = True
        Unload UserForm1
    End If
End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

        If CloseMode = 0 Then ActiveWorkbook.Close

End Sub

Could you tell me if there is a way for a manager to unhide anyother sheet than his own.

Many thanks.

 

[sastoka]

they are supposed to be able toupdate the data daily.