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.
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

jimboy

Well-known Member
Joined
Apr 11, 2002
Messages
2,314
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.
 

antony32

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

Well-known Member
Joined
Apr 11, 2002
Messages
2,314

ADVERTISEMENT

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
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,164
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123

ADVERTISEMENT

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

Well-known Member
Joined
Dec 5, 2002
Messages
8,164
Office Version
  1. 2016
Platform
  1. Windows
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.
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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

Well-known Member
Joined
May 19, 2002
Messages
541
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,965
Messages
5,621,871
Members
415,862
Latest member
nascaline

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
Top