Password to be unable to view worksheet

antony32

New Member
Joined
May 10, 2004
Messages
11
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.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Right mouse over the Sheet 4 (ie the sheet name) and select view code, then paste this in;

Code:
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.
 
Upvote 0
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)
 
Upvote 0
Try this;

Code:
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
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
lasw10 said:
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top