Hide/lock worksheets

  • Follow


I have a workbook with 10 spreadsheets. Each sheet/tab is for individual 
employees. Is there a way to have a user sign in and only view their own tab 
and not gain access or even hide the other employees sheets/tabs?

0
Reply Utf 1/7/2010 5:17:02 PM

You can have a sign-in sheet that would display when the file is opened. 
You could then have a drop-down in a cell that would display all the names. 
The user would select a name (his own, presumably) and his sheet would be 
displayed.  But what is there to prevent him from selecting another name 
than his own?  Well, Excel could ask for a password.  All of this would be 
based on the sheets being VeryHidden, a concept that would require the user 
to have some knowledge of VBA programming to defeat.  But be aware that 
Excel is not built to be a secure platform.  To the casual user yes.  To the 
knowledgeable user, no.  Come back if you want to pursue this further.  HTH 
Otto

"McChas" <McChas@discussions.microsoft.com> wrote in message 
news:33B82EFE-407B-4988-B6EF-3A574C9A2A17@microsoft.com...
> I have a workbook with 10 spreadsheets. Each sheet/tab is for individual
> employees. Is there a way to have a user sign in and only view their own 
> tab
> and not gain access or even hide the other employees sheets/tabs?
> 
0
Reply Otto 1/7/2010 5:36:53 PM


Hi,
Let's say sheet 1 can be access by user 1 so right click on the sheet1 tab, 
view code and there copy below code. Password is "MANAGER" you can copy the 
same code in all the other sheets and just change the password. Then hide the 
sheet and make a menu access like user name so when the user click on the 
button it will be asked for the password if the password is wrong will be 
returned to the main menu

Private Sub Worksheet_Activate()
Dim strPassword As String
On Error Resume Next
Me.Protect Password:="MANAGER"
Me.Columns.Hidden = True

strPassword = InputBox("Enter password to access DATA sheet")

If strPassword = "" Then
ActiveSheet.Visible = False
Worksheets("Menu").Select
Exit Sub
ElseIf strPassword <> "MANAGER" Then
MsgBox "Password Incorrect "
ActiveSheet.Visible = False
Worksheets("Menu").Select
Exit Sub
Else
Me.Unprotect Password:="MANAGER"
Me.Columns.Hidden = False
End If
Range("a1").Select
On Error GoTo 0
End Sub

Private Sub Worksheet_Deactivate()
On Error Resume Next
Me.Columns.Hidden = True
On Error GoTo 0
End Sub


When creating the Menu to access the file you can assign the macro as follow 
to the button

Sub Sheet1()
'
' Sheet1 Macro
'
Sheets("Menu").Select
    Sheets("Sheet1").Visible = True
    Sheets("Sheet1").Select

"McChas" wrote:

> I have a workbook with 10 spreadsheets. Each sheet/tab is for individual 
> employees. Is there a way to have a user sign in and only view their own tab 
> and not gain access or even hide the other employees sheets/tabs?
> 
0
Reply Utf 1/7/2010 5:42:01 PM

Not too easily.  A really dedicated user can crack Excel's security.

Requires VBA code and some passwords or login names.

Sample code.......................

Note: the following is contingent upon users enabling macros.

If they don't only the "Dummy" sheet will be visible with a large message
stating "By disabling macros you have rendered this workbook unusuable.
Please close and re-open with macros enabled"

I assume you are on a network(LAN) with users logging into the system.

I would set it up so that whichever user's login name is flagged, all sheets
except that user would be hidden.

No password to open the workbook or sheet protection, just code to make a
user's sheet visible.

In the Thisworkbook Module....................

Private Sub Workbook_Open()
Dim pword As String
On Error GoTo endit
Select Case Environ("Username")

      'if a login is not used change to
      'pword = InputBox("Enter Your Password")
      'Select Case pword
      
      Case Is = "Gord": Sheets("Gordsheet").Visible = True
      Case Is = "Pete": Sheets("Petesheet").Visible = True
End Select
Sheets("Dummy").Visible = False
Exit Sub
endit:
'MsgBox "Incorrect Password"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sht As Worksheet
Application.ScreenUpdating = False
       Sheets("Dummy").Visible = xlSheetVisible
           For Each sht In ActiveWorkbook.Sheets
           If sht.Name <> "Dummy" Then
      sht.Visible = xlSheetVeryHidden
End If
Next sht
Application.ScreenUpdating = True
ThisWorkbook.Save
End Sub

To allow you to see all sheets and edit them.

In a general module...............

Sub UnHideAllSheets()
    Application.ScreenUpdating = False
    Dim n As Single
    For n = 1 To Sheets.Count
        Sheets(n).Visible = True
    Next n
    Application.ScreenUpdating = True
End Sub

Naturally you want all this code invisible to the users.

Right-click on the workbook/project in VBE and select VBAProject Properties
and "Lock project for viewing"

Enter a password.


Gord Dibben  MS Excel MVP

On Thu, 7 Jan 2010 09:17:02 -0800, McChas <McChas@discussions.microsoft.com>
wrote:

>I have a workbook with 10 spreadsheets. Each sheet/tab is for individual 
>employees. Is there a way to have a user sign in and only view their own tab 
>and not gain access or even hide the other employees sheets/tabs?

0
Reply Gord 1/7/2010 5:42:33 PM

How much security do you need? Excel does possess adequate security features 
to stand-up to a medium-large security attack.

Some basic ideas that are possible:
a) have a macro copy one sheet from master workbook into a new workbook.
b) In VBE, set visible property of other user sheets to "xlSheetVeryHidden"

But again, these are very weak security. The safest bet would be to split up 
the workbook (See Ron's site for help on this: 
http://www.rondebruin.nl/copy6.htm) and then place them each into folders 
that only each user can access.
-- 
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"McChas" wrote:

> I have a workbook with 10 spreadsheets. Each sheet/tab is for individual 
> employees. Is there a way to have a user sign in and only view their own tab 
> and not gain access or even hide the other employees sheets/tabs?
> 
0
Reply Utf 1/7/2010 5:50:03 PM

4 Replies
1853 Views

(page loaded in 0.068 seconds)


Reply: