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.

 

[antony32]

I have a workbook that is set up on my network at work that numerous people input data.

The problem is that i want each person to be asked for a password when they open the workbook, and dependant on the password they give, it gives them access to certain pages.

Example:

Staff Member 1 - Needs access to sheet 1 & 3

Staff Member 2 - Needs access to Sheet 2

Supervisor 1 - Needs access to sheets 1, 2, 3 & 4.

I also need to ensure that if they choose to disable the macros, they don't have access to any of the pages.

 

[DonkeyOte]

antony no one is trying to be a pain but in fairness you could do what you ask for using all of the code previously given. Also, if someone comes up with a solution that would probably use half of the stuff already given.

We've told you how to store userid's, and how to match the id - it's not beyond the realms of possibility to include additional columns to this login table containing password, sheet access - then simply amend the workbook open event you were given earlier on to get the password etc.... use an inputbox (see VBA help) to get the user to enter a password - compare them. Ensure you have a close event that sets visibility to xlveryhidden on those sheets you would like hidden if VBA disabled.

All of the above is contained within this post. Don't ask people to repeat information already given.

 

[antony32]

Not sure what your issue is here....I have been using VB for about 2 days now so am a VERY EARLY beginner. I do not know how to modify code, which is why i was asking for help in that, not just getting the correct code and using it. You may remember when you first started to use Excel, & that everything looked very confusing to you. I do apologise for obviously being so far behind you in learning Excel.....

Thanks for making me decide not to use this website again...

 

[DonkeyOte]

no problem.

leave your high chair by the door on your way out. we don't spoonfeed.

 

[kidkl]

Come on guys..this is a discussion board..so let it be like that ..

 

[Chunky_22]

Searching through Mr Excel for my issue I found this old thread which is perfect, just what I need. However, there must be something missing in the code that I have copied from it. With the code below in ThisWorkbook, I click sheet 4, it disappears from view, and I get prompted for the password. I enter TEST in the box and it just asks me for the password again. It doesnt activate the sheet and end the macro. Can anyone point out where ive gone wrong please?


Dim LastActiveSheet As Worksheet


Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
If Not Sh Is Sheets("Sheet4") Then
Application.ScreenUpdating = False
Set LastActiveSheet = Sh
End If
End Sub


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh Is Sheets("Sheet4") Then
Sh.Visible = False
With Application
EnableEvents = False
LastActiveSheet.Activate
EnableEvents = True
End With
PromptForPassword
End If
End Sub


Sub PromptForPassword()
Dim UserInput As Variant
Const PWord1 As String = "TEST"
Const Msg1 As String = "Sheet Locked For Viewing !" & vbNewLine _
& vbNewLine & "Enter Password To Unlock."
Const Msg2 As String = "Wrong Password !"

With Application
Do
UserInput = .InputBox(Msg1)
Select Case UserInput
Case Is = False ' if user cancells don't activate sheet
Exit Do
Case Is = PWord1 '**if password correct activate sheet4
Set LastActiveSheet = Sheets("Sheet4")
Exit Do
Case Else 'if wrong password give user another try
UserDecision = MsgBox(Msg2, vbRetryCancel): Beep
End Select
Loop Until UserDecision = vbCancel

Sheets("Sheet4").Visible = True
EnableEvents = False
LastActiveSheet.Activate
EnableEvents = True
End With
End Sub