sastoka

Board Regular
Joined
Jun 14, 2014
Messages
193
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.


Code:
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

Code:
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.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Forum statistics

Threads
1,216,051
Messages
6,128,503
Members
449,455
Latest member
jesski

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top