Vba to password protect sheet

[jedoob]

Hi all,

Hope someone can help me. I've got a workbook and I need one sheet password protected (even from read).
I've managed to more or less do what I want with the following code (Note: This needs to work at a basic level not against a determined hacker!).

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim MySheets As String, Response As String
MySheet = "Headcount"
If ActiveSheet.Name = MySheet Then
ActiveSheet.Visible = False
    Response = InputBox("Enter password to view sheet")
        If Response = "password" Then
            Sheets(MySheet).Visible = True
            Application.EnableEvents = False
            Sheets(MySheet).Select
            Application.EnableEvents = True
        End If
End If
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
        Cancel As Boolean)
        MySheet1 = "Sheet1"
        MySheet2 = "Headcount"
        Worksheets(MySheet1).Visible = True
        Worksheets(MySheet2).Visible = xlVeryHidden
        End Sub

Basically this means that whenever someone click on the sheet it prompts for a password, and if anyone saves the file whilst the said sheet is active- then said sheet is 'veryhidden' immediately before save (this means that the next user can't see the sheet when they open the file, and also that they can't access the sheet if they disable macros, as there is no way to get the sheet to reappear without macro use.
(I've added a button to unhide the sheet if it is in veryhidden status, and you then need to enter the relevant password).

So all is good part from one thing. If I'm in another sheet and I start writing a formula like =SUM(....) and click on the passworded sheet whilst writing this formula then it shows me the full contents without any password prompting (which kind of defeats the purpose). I get why ... because the sheet is not activated whilst you are writing a formula - but I'm looking for a workaround this issue.

Many thanks in advance!

[alansidman]

Code Tags Added
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



(I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)