Password to be unable to view worksheet

antony32 · May 10, 2004

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.

antony32 · May 10, 2004

Please can someone run through & explain clearly how i set up 'XLVeryHidden', as you've hit the nail on the head. If the user chooses to disable macros, they can accesss all of the pages!

Many Thanks

OdinsDream · May 10, 2004

To set up a sheet to be xlSheetVeryHidden, right-click on the sheet name at the bottom of the workbook. Click "View Code". Then, hit F4.

In the spot labelled "Visible," select xlSheetVeryHidden.

To undo this with code:

Sheets("Sheet 1").Visible = xlSheetVisible

or, click on the sheet name in your Visual Basic editor, and edit the properties again.

Jaafar Tribak · May 10, 2004

Hin Antony,

if the sheet is hidden, how could the users activate it ??

You would still need some VBA code to unhide the sheet maybe via a Button or a commandbar control that the users can click on or by using the code I posted.

One solution is perhaps to set the xlveryHidden property as OdinsDream explains combined with the code I posted.

However if you choose this route, you will need to add this to the previous code:

Private Sub Workbook_Open()
Sheets("Sheet4").Visible = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Sheet4").Visible = xlVeryHidden
End Sub

Now if the user disables the macros, Sheet4 will remain hidden otherwise it will be visible only if correct password is provided.

I hope this is not overkill.

DonkeyOte · May 10, 2004

pending 2 things...

1. is this running on a network (presume so being a work thing)
2. how many people need access to sheet4?

if the answer to 1 is yes, and 2 is not variable - ie certain users have access...

easiest thing is to utilise the environment variables and extract the userid of the current user - note this is not the same as the application.username (ie the username assigned to the copy of XL being used) rather it is your login id for the network.

so you have a sheet (xlveryhidden) called login or something

in this sheet (col A) you have a list of those user ids allowed to view sheet 4

then simply check the userid when the file is opened to your list of approved users - if the user is allowed to view 4 then you unhide it, then as stated earlier re-xlveryhidden on close...

ex...

Code:

Private Sub Workbook_Open()

uid = environ("username")

On Error GoTo AccessDenied:
x = Application.WorksheetFunction.Match(uid,Sheets("login").Range("A:A"),0)

Sheets("Sheet4").Visible = True

AccessDenied:

End Sub

Code:

Private Sub Workbook_BeforeClose(Cancel As Boolean) 
Sheets("Sheet4").Visible = xlVeryHidden 
End Sub

So if VBA is disabled by default NO ONE can see Sheet4... if VBA enabled only approved users can see it - ie those whose user id appears in your list.

Jaafar Tribak · May 11, 2004

lasw10 said:
pending 2 things...

1. is this running on a network (presume so being a work thing)
2. how many people need access to sheet4?

if the answer to 1 is yes, and 2 is not variable - ie certain users have access...

easiest thing is to utilise the environment variables and extract the userid of the current user - note this is not the same as the application.username (ie the username assigned to the copy of XL being used) rather it is your login id for the network.

so you have a sheet (xlveryhidden) called login or something

in this sheet (col A) you have a list of those user ids allowed to view sheet 4

then simply check the userid when the file is opened to your list of approved users - if the user is allowed to view 4 then you unhide it, then as stated earlier re-xlveryhidden on close...

Laws10, That's definitely a much cleaner and elegant approach

I am not familiar with this VBA function, in particular the meaning of the environment variables. The online help being so slick

Do you know any other useful uses of this Function ?

Regards.

DonkeyOte · May 11, 2004

I'm not on a network presently but try this

Code:

Private Sub a()
Dim i As Integer

    i = 1
    Do
        If Environ(i) = "" Then Exit Do

        Cells(i, 1) = Environ(i)
        i = i + 1
    Loop


End Sub

antony32 · May 11, 2004

Thats great Jaafar, the code you've given does precisely what I required it to do.

What i'll now need to do now i've got the hang of it is to have the password protection on multiple sheets:

Example:

sheet1 - unlocked
sheet2 - unlocked
sheet3 - unlocked
sheet4 - password protected
sheet5 - unlocked
sheet6 - password protected
Sheet7 - password protected

What do i need to do to the code to enable this?

PS. if you're thinking i'm asking VERY simple questions, it'll be because i've been using VBA for about a day!

Danbrighton · May 11, 2004

Hi

Cant you just go to tools~protection then select sheet. This should then allow you to save the selected sheet rather than the workbook.

D

Jaafar Tribak · May 11, 2004

Antony,

Below is the adapted code to accomodate the 3 Sheets as requested :

Dim LockedSheets As Object
Dim LastActiveSheet As Worksheet
Dim S As Worksheet
Dim TempSheetVar As Worksheet
Dim UserInput As Variant
Const PWord1 As String = "Enter Password1 Here"
Const PWord2 As String = "Enter Password2 Here"
Const Msg1 As String = " Is Locked For Viewing !" & vbNewLine _
& vbNewLine & "Enter Password To Unlock."
Const Msg2 As String = "Wrong Password !"

Private Sub Workbook_BeforeClose(Cancel As Boolean)
 On Error Resume Next
 Application.ScreenUpdating = False
 For Each S In LockedSheets
 S.Visible = xlvryhidden
 Next S
 Me.Save 'This is to ensure hiding sheets is saved
End Sub

Private Sub Workbook_Open()
 On Error Resume Next
 Application.ScreenUpdating = False
 Set LockedSheets = Worksheets(Array("Sheet4", "Sheet6", "Sheet7"))
 For Each S In LockedSheets
 S.Visible = True
 Next S
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
 On Error Resume Next
 Set TempSheetVar = LockedSheets(Sh.Name)
 Set TempSheetVar = Nothing
 If Err.Number = 9 Then ' if Sh out of range store it in a global var
 Err.Clear
 Application.ScreenUpdating = False
 Set LastActiveSheet = Sh
 End If
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
 On Error Resume Next
 Set TempSheetVar = LockedSheets(Sh.Name)
 If Err.Number = 0 Then
 With Application
 .EnableEvents = False
 Sh.Visible = False
 LastActiveSheet.Activate
 .EnableEvents = True
 End With
 PromptForPassword TempSheetVar
 Set TempSheetVar = Nothing
 End If
 Err.Clear
End Sub

Sub PromptForPassword(LockedWorkSheet As Worksheet)
 With Application
 Do
 UserInput = .InputBox("'" & LockedWorkSheet.Name & "'" & Msg1)
 Select Case UserInput
 Case Is = False ' if user cancells don't activate sheet
 Exit Do
 Case Is = PWord1, PWord2 ' if password correct activate sheet4
 Set LastActiveSheet = LockedWorkSheet
 Exit Do
 Case Else 'if wrong password give user another try
 UserDecision = MsgBox(Msg2, vbRetryCancel): Beep
 End Select
 Loop Until UserDecision = vbCancel
 LockedWorkSheet.Visible = True
 .EnableEvents = False
 LastActiveSheet.Activate
 .EnableEvents = True
 End With
End Sub


Just paste this bunch of code on ThisWorkbook Module and close the workbook to save the changes.

Now when you reopen it, the code should come to live.

Note that if the user disables the Macros, the locked sheets remain Hidden.

Regards.

antony32 · May 11, 2004

What if I need each spreadsheet to be accessible by a 'master' password (one that will open any of the pages) & individual passwords for each page?

ie.

sheet1 - password abc OR xyz

sheet2 - password def OR xyz

sheet3 - password ghi OR xyz

I will have about 10 sheets within each database. If you could give me an example plus explain how to add extra sheets into it without having to find out the whole code, it'll be a great help!

Password to be unable to view worksheet

antony32

New Member

antony32

New Member

Excel Facts

OdinsDream

Well-known Member

Jaafar Tribak

Well-known Member

DonkeyOte

MrExcel MVP

Jaafar Tribak

Well-known Member

DonkeyOte

MrExcel MVP

antony32

New Member

Danbrighton

Board Regular

Jaafar Tribak

Well-known Member

antony32

New Member

Similar threads

Forum statistics

Share this page

Password to be unable to view worksheet

New Member

New Member

Excel Facts

Well-known Member

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

New Member

Board Regular

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock