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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,354
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Yes, if he is determined and vaguely knowledgeable about Excel.
 

sastoka

Board Regular
Joined
Jun 14, 2014
Messages
193
Thanks for the answer,

Can someone help me make it more secure ?

Thanks.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,354
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Board Regular
Joined
Jun 14, 2014
Messages
193

ADVERTISEMENT

Thanks RoryA

I'll do it in another way then.

Many thanks.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,900
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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

Board Regular
Joined
Jun 14, 2014
Messages
193

ADVERTISEMENT

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

Well-known Member
Joined
Oct 27, 2005
Messages
20,900
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,354
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Do the managers change the data you send them or just review it?
 

Forum statistics

Threads
1,141,478
Messages
5,706,610
Members
421,460
Latest member
Taamrak

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
Top