Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hide/lock worksheets
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" 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hide/lock worksheets
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hide/lock worksheets
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 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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hide/lock worksheets
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I lock the first tab of multiple worksheets | Excel Worksheet Functions | |||
Can I lock a worksheet and still be able to hide and show groups? | Excel Discussion (Misc queries) | |||
Hide and lock cell formulas without affecting Macro? | Excel Discussion (Misc queries) | |||
Lock Columns and Hide Formula's | Excel Worksheet Functions | |||
hide a column and lock it out | Excel Discussion (Misc queries) |