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

 

[Arts]

Ruddles thanks for the below, that seems like you did a lot of work !! So thanks you for that, I will give it a go soon as im snowed under at work with a bunch of other tasks but appreciate all you have done.

Ps i wasnt expecting you to say brill to my indepth knowledge of macros, lol

 

[Ruddles]

Heh!

S'okay, some of it was made up on the fly but most of it was cobbled together from code I already had working elsewhere. That's the advantage of keeping all one's code where it's easily searchable!

 

[Taul]

Ruddles, that is a fantastic bit of work, I would like to use it for training records at work.

Would it be possible to add a new page using a template page from within the same workbook rather than have a blank page?

So If I were to create a template page with column and row heights and formatting set, the new user would have their page set up according to the template page.

 

[Ruddles]

Try this:-

[FONT=Courier New][SIZE=1]Option Explicit
Option Compare Text[/SIZE][/FONT]
[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
  Dim reply As Integer
  Dim usr As String
  
  If Environ("username") = "[COLOR=red][B]Paul-H[/B][/COLOR]" Then
    reply = MsgBox("You are logged in as Administrator" & Space(15) & vbCrLf & vbCrLf _
          & Space(5) & "Click 'Yes' to run the security script" & Space(15) & vbCrLf & vbCrLf _
          & Space(5) & "Click 'No' to display all worksheets" & Space(15), vbYesNo + vbQuestion)
    If reply = vbNo Then
      For Each ws In Worksheets
        ws.Visible = True
      Next ws
      ThisWorkbook.Save
      Exit Sub
    End If
  End If
   
  Application.ScreenUpdating = False
  
  On Error Resume Next
  Sheets("Main").Visible = True
  Set ws = Sheets("Main")
  On Error GoTo 0
  
  If ws Is Nothing Then Sheets.Add.Name = "Main"
  
  usr = Environ("username")
  Set ws = Nothing
  On Error Resume Next
  Set ws = Sheets(usr)
  ws.Visible = True
  On Error GoTo 0
  
  If ws Is Nothing Then
[/SIZE][/FONT][FONT=Courier New][SIZE=1][COLOR=green]    ' user worksheet not found - try to create a new one using the template worksheet
[/COLOR]    On Error Resume Next
    Sheets("Template").Visible = True
    Set ws = Sheets("Template")
    On Error GoTo 0
    If Not ws Is Nothing Then
[/SIZE][/FONT][FONT=Courier New][SIZE=1][COLOR=green]      ' template worksheet found - create a new user worksheet
[/COLOR]      Sheets("Template").Copy After:=Sheets(Sheets.Count)
      ActiveSheet.Name = usr
      Set ws = Sheets(usr)
      Sheets("Template").Visible = xlVeryHidden
    Else
[/SIZE][/FONT][FONT=Courier New][SIZE=1][COLOR=green]      ' template worksheet not found - create blank sheet
[/COLOR]      Sheets.Add.Name = usr
    End If
  End If
  
[/SIZE][/FONT][FONT=Courier New][SIZE=1][COLOR=green]  ' shuffle Main & Template worksheets to the front
[/COLOR]  For Each ws In Worksheets
    ws.Visible = True
    If ws.Name = "Main" Then ws.Move Before:=Sheets(1)
    If ws.Name = "Template" Then ws.Move After:=Sheets(1)
  Next ws
  For Each ws In Worksheets
    If ws.Name <> usr Then ws.Visible = xlVeryHidden
  Next ws
  
  ThisWorkbook.Save
  
  Application.ScreenUpdating = False
    
End Sub[/SIZE][/FONT]
[FONT=Courier New][SIZE=1][/SIZE][/FONT] 
[FONT=Courier New][SIZE=1]Private Sub Workbook_BeforeClose(Cancel As Boolean)[/SIZE][/FONT]
[FONT=Courier New][SIZE=1][/SIZE][/FONT] 
[FONT=Courier New][SIZE=1]  Dim ws As Worksheet
  Dim rep As Integer
  
  If ThisWorkbook.Saved = False Then
    rep = MsgBox("You must save this workbook if you want your worksheet to remain hidden." _
        & vbCrLf & vbCrLf _
        & "Do you want to save the changes you made to '" & ThisWorkbook.Name & "'?", vbYesNoCancel)
    If rep = vbCancel Then Cancel = True: Exit Sub
    If rep = vbNo Then ThisWorkbook.Saved = True: ThisWorkbook.Close
  End If
  
  On Error Resume Next
  Sheets("Main").Visible = True
  Set ws = Sheets("Main")
  On Error GoTo 0
  If ws Is Nothing Then Sheets.Add.Name = "Main"
  
  For Each ws In Worksheets
    If ws.Name <> "Main" Then ws.Visible = xlVeryHidden
  Next ws
  
  Sheets("Main").Visible = True
  
  ThisWorkbook.Save
  
End Sub[/SIZE][/FONT]

Create a template sheet called Template. (You can rename it and change the code later if you want to.) Change the bit in red to your username.

For testing, change the statement usr = Environ("username") to usr = "newperson" (or whatever), save the workbook, then re-open it to check a newperson sheet is created.

Report any glitches!

 

[Taul]

Thank you Ruddles, it's excellent! works a treat and no glitches.

Apologies to Arts for high-jacking your thread, it was too good to resist.

 

[Ruddles]

works a treat and no glitches.

There will be - it's just a question of how long it takes you to find them!

 

[jaypatel]

Hi

Question for Ruddles.....

How can you have 1 user viewing 2 or more spreadsheets? ie if i logged in as jpatel, can i view fred, Andy and Ian's tabs?

Cheers

Jay

 

[Ruddles]

This line:-

[SIZE=1]  If Environ("username") = "[COLOR=red][B]Paul-H[/B][/COLOR]" Then
[/SIZE]

checks to see whether the workbook 'administrator' is logged in. The workbook 'administrator' means whatever you want it to mean but if you put his username in that test, when the workbook opens up it will offer him the choice of (a) running the security check and hiding every worksheet except his own or (b) bypassing the scripts and making all the worksheets visible.

If you need more than one 'administrator' defined, you'd do something like this:-

[SIZE=1]  If Environ("username") = "[COLOR=red][B]Fred[/B][/COLOR]" Or Environ("username") = "[COLOR=red][B]Barney[/B][/COLOR]" Then
[/SIZE]

The other thing you could do is create a new standard code moduke and paste this code into it:-

[FONT=Courier New][SIZE=1]Option Explicit[/SIZE][/FONT]
[FONT=Courier New][SIZE=1][/SIZE][/FONT] 
[FONT=Courier New][SIZE=1]Private Sub ShowMe()[/SIZE][/FONT]
[FONT=Courier New][SIZE=1][/SIZE][/FONT] 
[FONT=Courier New][SIZE=1]  Dim ws As Worksheet
  
  For Each ws In Worksheets
    ws.Visible = True
  Next ws[/SIZE][/FONT]
[FONT=Courier New][SIZE=1][/SIZE][/FONT] 
[FONT=Courier New][SIZE=1]End Sub
[/SIZE][/FONT]

Then if you ever want to see all the worksheets, go Developer > Macros, type in the name ShowMe and click Run. By making it a Private Sub, it won't appear in the list of macros, so as long as you don't tell anyone the name of it, no-one else will be able to call it.

By the way, it's probably a good idea when running a system like this to protect the VBA project to stop nosey people poking around in the code.