Password Tab Protection

[Acoustic]

hi all,
i have read through a few posts and blogs but havent found what im chasing if it is at all possible

I have a work book in which i allow access by all, but on one of the sheets(tabs) i only want a selected few to be able to even view the tab preferably by password. i can see that its possible to protect it "structure" but this still allows viewing

hope someone can help

cheers

 

[GTO]

Is VBA okay, and will at least the users who you want the sheet to display for, enable macros?

 

[lundbhaiz]

change the visible property of sheet to xlveryhidden (2) using vba.

 

[Acoustic]

i think VBA will be fine as i have quite a few macros in which everyone knows to enable macros.
Is this an easy VBA code as im not highly skilled in that area

cheers

 

[GTO]

Not well tested, but along the lines of this:

Option Explicit
    
Dim bolInProcess As Boolean
    
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
    '// If the wb is saved, nothing needed.                                             //
    If Not ThisWorkbook.Saved Then
        
        '// If not saved, substitute our own handling, by first cancelling (momentarily)//
        '// the closing, then...                                                        //
        Cancel = True
        
        '// Ask user 'Save?'                                                            //
        If MsgBox("The workbook is not saved.  Save?", _
                  vbYesNo Or vbQuestion, _
                  "Save Workbook?" _
                  ) = vbYes Then
            
            '// Call the event to handle the save...                                    //
            Workbook_BeforeSave False, False
            ThisWorkbook.Saved = True
            ThisWorkbook.Close False
        Else
            '// ...or if user decided against saving, just mark the file as saved and   //
            '// close file, which will recurse to before_close, but will fail the IF    //
            '// and close without further ado.                                          //
            ThisWorkbook.Saved = True
            ThisWorkbook.Close False
        End If
    End If
End Sub
    
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    
    '// Set a flag to handle recurse.                                                   //
    If Not bolInProcess Then
        bolInProcess = True
        '// Hide the sheet of interest.                                                 //
        shtSheet2.Visible = xlSheetVeryHidden
        '// Save the wb with the sheet hidden.  This will breakout of the event, save   //
        '// wo/recursing to here (due to our flag), then...                             //
        ThisWorkbook.Save
        '// ...we'll Cancel the first called Save.                                      //
        Cancel = True
        
        '// Check to see if its a user we want to see our 'secret squirrel' sheet.      //
        If Environ("UserName") = "stumpm" _
        Or Environ("UserName") = "SomeoneElse" Then
            '// If so, show the sheet and mark the file saved, to prevent unnecessary   //
            '// asking of the user if they later close w/no other changes.              //
            shtSheet2.Visible = xlSheetVisible
            ThisWorkbook.Saved = True
        End If
        '// Reset our Bool                                                              //
        bolInProcess = False
    End If
    
    '   In gist - the above creates a pseudo After_Save event if you will.  The workbook//
    '// is never in a Saved state with the sheet showing.                               //
End Sub
    
Private Sub Workbook_Open()
    
    '// See if we want to show the sheet.                                               //
    If Environ("UserName") = "stumpm" _
    Or Environ("UserName") = "SomeoneElse" Then
        
        shtSheet2.Visible = xlSheetVisible
        ThisWorkbook.Saved = True
    End If
End Sub

I did not make any effort at handling a SaveAs, but hopefully this gives you a start.

Hope that helps,

Mark

 

[GTO]

ACK!

The above goes in the ThisWorkbook Module...

 

[Acoustic]

cheers Mark, will give it a go