Password to be unable to view worksheet

[antony32]

I am currently setting up a spreadsheet where i need one of the worksheets to be locked & unable to be viewed unless a password is inputted.

Is this at all possible within Excel?

For Example.

Sheet 1 = Staff member 1

Sheet 2 = Staff member 2

Sheet 3 = Staff member 3

Sheet 4 = Supervisor

I need Sheet 4 to be locked & unable to even be viewed unless a password is entered.

 

[jimboy]

Right mouse over the Sheet 4 (ie the sheet name) and select view code, then paste this in;

Private Sub Worksheet_Activate()

Range("A65536").Select
'change the password here (ABC)
If InputBox("What is the password?", "Password") = "ABC" Then
Range("A1").Select
Else
MsgBox ("Password incorrect")
Sheets("Sheet1").Select
End If

End Sub

When the sheet is selected, cell A65536 will be selected and a inputbox for a the password shown, enter "ABC" (without quotes) and range A1 will be select - enter the wrong password and sheet1 will be selected.

 

[antony32]

Fantastic! Thanks a lot for your help

 

[antony32]

ok, now onto the next question!

From the calculation given above, what do i need to add to enable the sheet to be accessible via two different passwords?

(i don't mean you need to enter both passwords, either of them will access it)

 

[jimboy]

Try this;

Private Sub Worksheet_Activate()
Dim mypassword As String
Range("A65536").Select
mypassword = InputBox("What is the password?", "Password")
'Change passwords here                         and here
If mypassword = "ABC" Or mypassword = "XYZ" Then
Range("A1").Select
Else
MsgBox ("Password incorrect")
Sheets("Sheet1").Select
End If

End Sub

 

[Jaafar Tribak]

Hi,

Below is an adapted version of a similar code I wrote before:

Place it in the ThisWorkbook module:


<font face=Courier New><SPAN style="color:#00007F">Dim</SPAN> LastActiveSheet <SPAN style="color:#00007F">As</SPAN> Worksheet


<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_SheetDeactivate(<SPAN style="color:#00007F">ByVal</SPAN> Sh <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>)
    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Sh <SPAN style="color:#00007F">Is</SPAN> Sheets("Sheet4") <SPAN style="color:#00007F">Then</SPAN>
        Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">Set</SPAN> LastActiveSheet = Sh
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>


<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_SheetActivate(<SPAN style="color:#00007F">ByVal</SPAN> Sh <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>)
    <SPAN style="color:#00007F">If</SPAN> Sh <SPAN style="color:#00007F">Is</SPAN> Sheets("Sheet4") <SPAN style="color:#00007F">Then</SPAN>
        Sh.Visible = <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">With</SPAN> Application
            .EnableEvents = <SPAN style="color:#00007F">False</SPAN>
            LastActiveSheet.Activate
            .EnableEvents = <SPAN style="color:#00007F">True</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        PromptForPassword
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>


<SPAN style="color:#00007F">Sub</SPAN> PromptForPassword()
    <SPAN style="color:#00007F">Dim</SPAN> UserInput <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
    <SPAN style="color:#00007F">Const</SPAN> PWord1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "Enter Password1 Here"
    <SPAN style="color:#00007F">Const</SPAN> PWord2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "Enter Password2 Here"
    <SPAN style="color:#00007F">Const</SPAN> Msg1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "Sheet Locked For Viewing !" & vbNewLine _
    & vbNewLine & "Enter Password To Unlock."
    <SPAN style="color:#00007F">Const</SPAN> Msg2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "Wrong Password !"
    
    <SPAN style="color:#00007F">With</SPAN> Application
        <SPAN style="color:#00007F">Do</SPAN>
            UserInput = .InputBox(Msg1)
            <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> UserInput
                <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#007F00">' if user cancells don't activate sheet</SPAN>
                    <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Do</SPAN>
                <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> = PWord1, PWord2 <SPAN style="color:#007F00">'  if password correct activate sheet4</SPAN>
                    <SPAN style="color:#00007F">Set</SPAN> LastActiveSheet = Sheets("Sheet4")
                    <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Do</SPAN>
                <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN> <SPAN style="color:#007F00">'if wrong password give user another try</SPAN>
                    UserDecision = MsgBox(Msg2, vbRetryCancel): Beep
                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
        <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">Until</SPAN> UserDecision = vbCancel
        
        Sheets("Sheet4").Visible = <SPAN style="color:#00007F">True</SPAN>
        .EnableEvents = <SPAN style="color:#00007F">False</SPAN>
        LastActiveSheet.Activate
        .EnableEvents = <SPAN style="color:#00007F">True</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>


Regards.

 

[DonkeyOte]

suggest you use

Sheets(...).Visible = xlVeryHidden as opposed to just False.

VeryHidden can only be unhidden using VBA - ie cracking workbook / sheet password will not allow user to view hidden sheet(s)

 

[Jaafar Tribak]

suggest you use

Sheets(...).Visible = xlVeryHidden as opposed to just False.

VeryHidden can only be unhidden using VBA - ie cracking workbook / sheet password will not allow user to view hidden sheet(s)

Hi Laws10,

In the scenario above there is no need to set the visible property to xlVeryHidden. The user cannot unhide the worksheet while the InputBox is active anyway.


Regards.

 

[DonkeyOte]

Well I'd disagree - sounds to me if you're password protecting your sheet you would rather people couldn't see it than could - ie when I close the file as it's owner I would xlveryhidden that sheet so if someone opens the file with either a) a basic grasp of XL VBA events or b) VBA Security set to High that the sheet is invisible and is not reliant on VBA to Hide it.

Basic logic I think?

 

[OdinsDream]

What happens if someone clicks No on the Macro Security warning? It seems like making the sheet xlVeryHidden would be a necessity, in that case, since the user would easily have disabled the sheet's VB code routines.