Password for tabs

[Arts]

Hi All

Would anyone know if it is possible for a password to view individual
tabs on a file. I know it is possible to ask for a password to open a file but is it possible to do this on each of the tabs on a work sheet so individuals can access their own tabs ? (without VBA)

I am using excel 2007

 

[Ruddles]

You could hide all the worksheets in the workbook, leaving just one sheet which invites them to enter their password. Depending on the password, you then unhide their sheet.

On the other hand, since they've presumably already logged in to Windows, you could user Environ("username") to check who they are and unhide their sheet without them having to enter anything. They need never even know there's more than one worksheet in the workbook.

Whichever you go for, you'd need to use VBA.

 

[Arts]

Thanks for the reply Ruddles,

I know you can put a password on the file when you go to saveas, tools...but just that the whole file doesnt need to have a password just particular tabs on there...i am not a fan of VBA in the sense I have no idea what to do with it and incase it fails on me for what ever reason i'd be in a bad place

Thanks anyway for the reply

 

[ndendrinos]

Hello Arts
To do what you ask you need VBA
As the admin using the password "monia" you get to see all tabs
For others to view their "personal" tabs they will have to enter the password indicated on each sheet
.
Download the sample file from here:
http://www.box.net/shared/ioysat691i

 

[Arts]

Hi ndendrinos

i suspected as much, i just thought that on excel 2007 there would have been an option to password protect the tabs as well, thanks none the less for the reply

 

[Ruddles]

The Environ("username") option is simple to understand, simple to implement, can be made bomb-proof and will need no maintenance. The amount of effort involved in adding the code is minimal.

 

[Arts]

Ruddles I'm game if you are

How and what is a "Environ("username")" ?

I apologise in advance for the truck load of questions that will come your way

 

[Ruddles]

It returns the username of the person currently logged in. When the workbook opens it can check who's logged in and displays just that person's worksheet.

If I put together some code, will you know how to add it to a workbook?

 

[Arts]

Im a bit familiar with it,

I know that pressing ALT F11 will open up the Microsoft VB and in sheet 1 I paste the code ?

So in short im not sure if thats a yes or no to your question

 

[Ruddles]

Brill! Create a new workbook and rename the first worksheet Main. Create a big 'splash' message saying, "If you can read this, then you have disabled macros. If you can see an on-screen prompt to enable macros, please do so now. Otherwise you will have to close this workbook and re-open it, enabling macros in the process."

Rename a second worksheet so it's the same as your Windows username and rename a third worksheet Fred. Press Alt-F11, then paste the following code into the code window for ThisWorkbook, changing the bit in red to be the same as your Windows username:-

[FONT=Courier New][SIZE=1]Option Explicit[/SIZE][/FONT] 
[SIZE=1][FONT=Courier New]Option Compare Text[/FONT][/SIZE]
 
[FONT=Courier New][SIZE=1]Private Sub Workbook_BeforeClose(Cancel As Boolean)[/SIZE][/FONT]
 
[FONT=Courier New][SIZE=1]Dim ws As Worksheet[/SIZE][/FONT]
[SIZE=1][FONT=Courier New]Dim rep As Integer[/FONT][/SIZE]
 
[SIZE=1][FONT=Courier New]If ThisWorkbook.Saved = False Then[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]  rep = MsgBox("You must save this workbook if you want your worksheet to remain hidden." _[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]      & vbCrLf & vbCrLf _[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]      & "Do you want to save the changes you made to '" & ThisWorkbook.Name & "'?", vbYesNoCancel)[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]  If rep = vbCancel Then Cancel = True: Exit Sub[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]  If rep = vbNo Then ThisWorkbook.Saved = True: ThisWorkbook.Close[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]End If[/FONT][/SIZE]
 
[SIZE=1][FONT=Courier New]On Error Resume Next[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]Sheets("Main").Visible = True[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]Set ws = Sheets("Main")[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]On Error GoTo 0[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]If ws Is Nothing Then Sheets.Add.Name = "Main"[/FONT][/SIZE]
 
[SIZE=1][FONT=Courier New]For Each ws In Worksheets[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]  If ws.Name <> "Main" Then ws.Visible = xlVeryHidden[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]Next ws[/FONT][/SIZE]
 
[SIZE=1][FONT=Courier New]Sheets("Main").Visible = True[/FONT][/SIZE]
 
[SIZE=1][FONT=Courier New]ThisWorkbook.Save[/FONT][/SIZE]
 
[SIZE=1][FONT=Courier New]End Sub[/FONT][/SIZE]
[FONT=Courier New][SIZE=1][/SIZE][/FONT] 
[FONT=Courier New][SIZE=1]Private Sub Workbook_Open()[/SIZE][/FONT]
[FONT=Courier New][SIZE=1][/SIZE][/FONT] 
[FONT=Courier New][SIZE=1]Dim ws As Worksheet[/SIZE][/FONT]
[SIZE=1][FONT=Courier New]Dim reply As Integer[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]Dim usr As String[/FONT][/SIZE]
 
[SIZE=1][FONT=Courier New]If Environ("username") = "[COLOR=red][B]Arts[/B][/COLOR]" Then[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]  reply = MsgBox("You are logged in as Administrator" & Space(15) & vbCrLf & vbCrLf _[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]        & Space(5) & "Click 'Yes' to run the security script" & Space(15) & vbCrLf & vbCrLf _[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]        & Space(5) & "Click 'No' to display all worksheets" & Space(15), vbYesNo + vbQuestion)[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]  If reply = vbNo Then[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]    For Each ws In Worksheets[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]      ws.Visible = True[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]    Next ws[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]    Exit Sub[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]  End If[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]End If[/FONT][/SIZE]
 
[SIZE=1][FONT=Courier New]On Error Resume Next[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]Sheets("Main").Visible = True[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]Set ws = Sheets("Main")[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]On Error GoTo 0[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]If ws Is Nothing Then Sheets.Add.Name = "Main"[/FONT][/SIZE]
 
[SIZE=1][FONT=Courier New]For Each ws In Worksheets[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]  If ws.Name <> "Main" Then ws.Visible = xlVeryHidden[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]Next ws[/FONT][/SIZE]
 
[SIZE=1][FONT=Courier New]usr = Environ("username")[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]On Error Resume Next[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]Set ws = Sheets(usr)[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]ws.Visible = True[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]On Error GoTo 0[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]If ws Is Nothing Then Sheets.Add.Name = usr[/FONT][/SIZE]
 
[SIZE=1][FONT=Courier New]Sheets("Main").Visible = xlVeryHidden[/FONT][/SIZE]
 
[SIZE=1][FONT=Courier New]ThisWorkbook.Saved = True[/FONT][/SIZE]
 
[SIZE=1][FONT=Courier New]End Sub[/FONT][/SIZE]

If you're unsure exactly what your Windows username is, type ?environ("username") in the VBA Immediate window (Ctrl-G).

Save the workbook and close it. When it's opened again, it will check the username of the person and switch to his worksheet if it exists. If it doesn't exist, one will be created with the correct name.

If you - the person whose name was inserted in the code in place of the bit in red - open the workbook, you will be prompted whether you want to run the security process so it will behave just like it would for any other user(click 'Yes') or whether you want to skip that and display all the worksheets - a sort of 'admin mode' (click 'No'). No-one else will get this prompt.

Finally ask someone else to open the workbook: they should be presented with a new blank worksheet. No-one should ever see Fred (unless there's a username Fred in your domain and you ask him to try it).

See how that goes...

The same script can be added to existing workbooks by pasting the code in the ThisWorkbook code window, naming the worksheets to match the owners' usernames and creating a Main sheet with a big 'splash' message.