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.

 

[RoryA]

Yes, if he is determined and vaguely knowledgeable about Excel.

 

[sastoka]

Thanks for the answer,

Can someone help me make it more secure ?

Thanks.

 

[RoryA]

Excel is not a secure environment. You could perhaps do it using an application that compiles the workbook into an executable, but it's easier to only distribute the relevant information to each person.

 

[sastoka]

Thanks RoryA

I'll do it in another way then.

Many thanks.

 

[Michael M]

Further to RoryA's comments, have you considered e-mailing the relevant sheets to each manager rather than giving them access to the whole workbook ??

 

[sastoka]

It might be a solution but as the administrator of the file i need to keep a summary which is currently in the workbook. My problem would be how to keep it up to date if I'm sending each worksheet to manager ? If you have a way I'd be happy to hear it.

 

[Michael M]

No, it was just a thought for an alternative !

I guess you have to ask yourself....
Do the other managers really care ??
Does it mattter if they see the data ??
How Excel savvy are they ??
If not very, then it's unlikely they would know how to access the other sheets or go to the trouble to find out !

As RoryA mentioned Excel security is weak....AND...passwords are easily bypassed / broken.

 

[sastoka]

Out of the 13 different mangers most of them wont care, but the HR and finance manager will as this file regroup thing like hour rate and other.
ANd I know at least 3 manger that have good VBA knowledge.

so it doesnt look like a good idea to me if it's that easy to bypass.

but thanks for everything.

 

[RoryA]

Do the managers change the data you send them or just review it?