Hide/lock worksheets

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?

1/7/2010 5:17:02 PM
excel.worksheet.functions 4936 articles. 0 followers. Follow

4 Replies

Similar Articles

[PageSpeed] 42

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 

"McChas" <McChas@discussions.microsoft.com> wrote in message 
> 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?
1/7/2010 5:36:53 PM
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
Exit Sub
ElseIf strPassword <> "MANAGER" Then
MsgBox "Password Incorrect "
ActiveSheet.Visible = False
Exit Sub
Me.Unprotect Password:="MANAGER"
Me.Columns.Hidden = False
End If
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("Sheet1").Visible = True

"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?
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
'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
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>

>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?

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?
1/7/2010 5:50:03 PM

Similar Artilces:

Worksheets and Save
Question 1: In Excel 2000, I'm working with a workbook with many worksheets. How can I see the contents of Sheet1 and Sheet2 (or more) at the same time? (Similar to Window/Arrange/Vertical). ============================= Question 2: When I save a workbook, the Status bar shows: 1. "Saving" plus the filename then 2. MICROSOFT EXCEL 5.0/7.0 and "Saving" plus the filename. Is the workbook being saved twice? Do "Window|New Window" first. Then the arrange stuff will work nicely. To close one of the windows, just click on it and hit ctrl-F4. And you...

Hiding a workbook
Hello, What is the VBA code to hide a wookbook while running a VBA macro? -- Regards, Jeff ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=8ca8e508-528b-48b7-8e20-09c67eb491fa&dg=microsoft.public.e...

Locked out of database
Not sure what happened but get a message saying "database has been converted from a prior version of Access by using DAO compactdatabase and is now in a partially converted state." It says I should be able to import tables into a new data base but when I try this it just gives the same message about converting and the data base just doesn't open. Please help because lots at stake, have to do a report based on these tables so am waiting with anticipation!!!x Hi Pete, Usually this means you are opening an earlier version of Access in a new version. Make a copy of the data...

Hide folders
Vista Premium SP2 Is it possible to hide some of the C:\users\myname folders? "EndUser" <my@add.res> wrote: >Vista Premium SP2 >Is it possible to hide some of the C:\users\myname folders? Have you tried right-clicking the folder, choosing "Properties", and checking the "Hidden" checkbox? -- Tim Slattery Slattery_T@bls.gov http://members.cox.net/slatteryt "Tim Slattery" <Slattery_T@bls.gov> news:lpdki59vnllk9f7rbvn9vjoi7uhokar84b@4ax.com... > "EndUser" <my@add.res> wrote: > >>Vista P...

hide activities
Hi, I'd like to hide the service appointment and campaign response activities options when I want to create a new activity. I was change the dlg_create.aspx file and comment the render list item. I don't like the idea to manipulate this file, and I want to know if is possible to hide this options in other way. thanks -- Magy yes you can hid see your id's document.all.navContacts.style.display = "none"; -- Regards, MS CRM Certified Professional http://microsoftcrm3.blogspot.com Chat with me on MSN / Gmail / Skype : ID Is :.. mscrmexpert@gmail.com "...

I have two non-numerical data worksheets. I need to link specific cells to eachother across these two worksheets. Almost like a hyperlink. Is there any way that I can do this??? Please email. You can link these by just typing the = sign in a cell, then navigate to the other workbook or worksheet, and click in the desired cell you want linked. Then hit Enter. Or you could click in a cell, select Insert, Hyperlink, and make choices as desired in the Insert Hyperlink box. MRO "Laurie" <laurie.egbert@mris.net> wrote in message news:01bc01c37c76$d3ee70b0$a301280a@phx.gbl......

WOrksheets 12-21-09
I have one file with two worksheets. They are both the same, but one worksheet needs to be re arranged to be easier to read alter information while the other has to remain in a certain format so it can be uploaded into my accounting system. They have been successfully linked, but when I g to re arrange the first worksheet, it automatically re arranges the second worksheet when I dont want it to. The current formula I have is =Worksheet!$D$2 Thanks Hi Smirlface, Re-arrange the second worksheet instead. -- Cheers macropod [Microsoft MVP - Word] "Smirlface...

Multipule Worksheets
How do you set Excel 2002 to open all spreadsheets into one document instead of having multipule documents open up across your toolbar? ...

Hiding file
Hi, I was working with a file Excel 2003. The, all of a sudden, it disappeared from the screen. It seems that have hit certain key combinations that did this. I re-oppened the file (bouble click), Excel opens and the files vanished again. I am not sure if it is "closed" or being "hidden". On the left bottom side of the screen I have the word "ready". Any ideas, The file has important information. If you do file|print preview, do you see anything? If yes... I'd try: Windows|Arrange|Tiled and rearrange the way you like. If no, maybe the window is hidde...

Want info in a specific cell to reflect in another worksheet..
How do I get a Numerical Value on Worksheet 1 cell C3 to reflect in Worsheet 2 cell C5? Thank you! A good way to learn. in the desired cell put in = now goto the other sheet and cell then hit the enter key -- Don Guillett SalesAid Software donaldb@281.com "Shiren" <Shiren@discussions.microsoft.com> wrote in message news:5C7B279C-4FE0-45E6-8876-C2F61EF0497F@microsoft.com... > How do I get a Numerical Value on Worksheet 1 cell C3 to reflect in Worsheet > 2 cell C5? > > Thank you! In worksheet 2 cell C5 enter ='worksheet 1'!C3 Gord Dibben Excel MVP ...

printing multiple copies of a worksheet
:mad: I spent the last 2 hours printing 20 copies of one single worksheet. Excel sent each copy as its own print job so I had to wait for my printer to spool 20 individual print jobs. This is ridiculous. Am I missing a print setting somewhere or do I just have to live with this problem. Any help would be greatly appreciated. -- bloodgroove ------------------------------------------------------------------------ bloodgroove's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=18281 View this thread: http://www.excelforum.com/showthread.php?threadid=390610 I...

Logon Failure event of a locked out account
I have SBS 2003 SP1. A tech that occasionally works on our server remotely built an account called staff. When he doesn't need to use it I disable the account, lock it out and change the password, but I keep getting this error in groups of two or three. Should I be worried that someone is trying to hack in? Event Type: Failure Audit Event Source: Security Event Category: Logon/Logoff Event ID: 539 Date: 6/25/2010 Time: 8:21:56 AM User: NT AUTHORITY\SYSTEM Computer: SCAPCADC Description: Logon Failure: Reason: Account locked out User Name: staff Domain: ...

Hiding the Source code In Modified Forms
Hi, We used Modifier and VBA to modify some forms is Great Plains (Rel 7.0). New text boxes and labeles were added to the form and custom code has also been written. After modifying the forms, we exported the same into a package. This was then imported at the client's place using the Customization Maintenance option and the modified forms were installed. However, we find that when some error occured in the modified forms, the control opens the code window and comes to the line in the code where the error has occured and the all the code that we have written is visible. We have wri...

Hide Unhide Hide again
Hello, I'm using Excel 2003. It seems in the older versions, when I hid a column or row, it would put a little "+" tab above the labels. You could easily "click" or "unclick" ("hide" or unhide") the columns or rows. I need to be able to hid these rows or columns, but unhide them for use and then hid them again for ease of use and printing. Thank you. Hiding rows/columns and Grouping/Outline are quite different features. The little + sign occurs when you group (outline) it's not actually hiding rows Its under the DATA > GROUP AND O...

OLook Locks up when attempting to delete large file in out box.
Outlook keeps trying to send a file that is way too large. I didn't notice it was so large until after a few days of OL being extremely slow and using up my CPU at 100%. How can I delete this file? I have tried going to the outbox and selecting delete, but it will not delete. Can find the location of the data, but not the file that needs to be deleted. Try the easy fix: Select File, Work Offline, Delete the messages "SNR" wrote: > Outlook keeps trying to send a file that is way too large. I didn't notice > it was so large until after a few days of OL being ext...

Dynamics POS 2.0 Locks Up (Not Responding)
Trying to figure out why my Dynamics locks up on me when it is inactive for a few minutes. I think it may have to do I am running it on a system that has Vista Home premium. I have 1GB Ram so I know that is not the problem. We started using it about a week ago, but the problem just started about 3 days ago. Can anyone help? I believe Vista Home edition is not supported. You'll need to move it on to a different platform, XP or Vista business. Cheers "shadowrunner11" wrote: > Trying to figure out why my Dynamics locks up on me when it is inactive for a > few minutes....

Math Worksheet Database
I am interested in developing/acquiring an Access 2003 database to generate (K-5 grade) math worksheets where I can track student results. I would hope not to reinvent the wheel. Thank you for any assistance. ...

Selecting Print area in protected worksheet
I have a worksheet where the users enter data, new rows etc. The calculations are at the bottom and I have protected them. However, when I do this the users can no longer set the print area to print. How do I solve this? DRB, I can set the print area with the sheet protected and or the workbook protected in 2003 with no problems ??? -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "DRB" <DRB@discussions.microsoft.com>...

Can i run VB code when a user changes Worksheets within an Excel Workbook
I have a Excel Spreadsheet with 6 worksheets in it The first sheet contains all the data and the other sheets do various things with the data (graphs etc) On the first sheet i have buttons above some of the table colums which sort the data by that colums. The buttons run VB code Now what i would be able to accomplish is if a user clicks on one of the other sheets, firstly I want to sort the data in Sheet 1 by the first column to get the data back in the correct order, otherwise the graphs are all wrong on the other sheets I need some kind of way to enable the following code to run when the...

Hiding Numbers
I have a column with numbers from formulas and from subtotals. I only want to display the subtotal numbers. Is there anyway to only display the subtotal numbers and nothing else within the same column? Thanks for any help you can provide! Hi If you have used the built-in Excel subtotal function, you can use the grouping number down the left-hand side to do this. Clicking 3 shows the grand total only, clicking 2 shows the group totals and clicking 1 shows all of the detail. -- Andy. "DNA" <dnoel@fsgbank.com> wrote in message news:eff161b985d93430d74eae934f898edf@loca...

Hiding VB Code
I am using Windows XP and Excel 2002. I have written a small code in THIS WORKBOOK. I want to know if there is a way to protect anyone else from going into VB editor and seeing the code? I'd like to make it not visible except to me or by password just for the code. Thanks in advance. Baz, In VB editor click the TOOLS menu, Project properties, Protection tab and set a password. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "baz" w...

Batch Print not hide Controls
This code works when I print a report singular but when I batch print (Which is another report) it will not hide these controls I have the same code on my other report The controls are in my page footer Thanks for any help......Bob Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) On Error GoTo ProcError lbBankName.Visible = Len([tbDirectBank] & vbNullString) > 0 lbBranch.Visible = Len([tbDirectBank] & vbNullString) > 0 lbAccountName.Visible = Len([tbDirectBank] & vbNullString) > 0 lbAccountNumber.Visible = Len([tbDirectBank] &...

lock tables
it is easy to go to the database window to double click on a table and open it. it is also possible to import or link tables of a secured database and open up the tables in a new database. is there any way i can secure (lock or hide) access tables so nobody can open it via the database window or via import and link? TIA If you "give" your users access to the database window, no. If you button down your application, create forms and reports, and have your users using those, you can discourage the casual lookie-lou. If you add Access security (a strenuous bit of exercise, not for...

Hide all Sheets
Greetings, I got alot of sheets on a workbook and every time the workbook i closed it hide all sheets except one that is a "logon", is there a vb command where I can hide all work sheets and then have it unhid "Logon" only or do I have too type each sheet into an array and use th visible=true command? I tried the array method and it works fine for hiding all sheets, bu when I tried too unhide it gives me an error, may be becuase it can' find the active sheets? Any help is appreciated -- Fabl ----------------------------------------------------------------------- ...

Hide Address List #2
I have create an address list of contacts in the ESM. I have granted full control to the following: Domain Admins Enterprise Admins Exchange Domain Servers SYSTEM As a member of the Domain Admins group, I am able to see the address list as well as list its contents in Outlook. When I log in on a test workstation with a test user, I am still able to see the list but not list its contents in Outlook. The test user is a member of only the Domain Users group. What more must I do to prevent the address list from even showing up in the list of address lists in Outlook for the test use...