Password required in VBA to open the sheet

[Tia]

Hey guys

I have a project workbook that contains the project data for each
employee and i have used the vba code so everytime u open the file the
main sheet is displayed and in it there is the name of all employees
when u press on the name you will open the sheet required. what i need
is a way that each time an employee press on his name to open his
sheet a password is asked to be put so each employee can only log to
his sheet with his password


What is the way to do that ?

Thank you in advance

Tia

 

[Mike H]

Hi,

This assumes you have a sheet called 'Menu' that you use to Hyperlink to the
employee sheets. Right click the sheet tab of sheet 2, view code and paste
the code below in. Every time sheet 2 is now activated you are prompted for
the password before it becomes visible.

This is a solution for a single sheet and you could paste this into every
sheet with the name/password changed or get a bit more complex and use the
Workbook_SheetActivate event and build generic code that works for every
sheet.

In practice I wouldn't do either. On the assumption you have employees of
reasonable capability any of them could crack this protection very easilly
and view other employee information.

Private Sub Worksheet_Activate()
Sheets("sheet2").Visible = False
response = InputBox("Enter Password", vbOKOnly)
If response = "MyPass" Then
Application.EnableEvents = False
Sheets("sheet2").Visible = True
Sheets("sheet2").Select
Application.EnableEvents = True
Else
Sheets("sheet2").Visible = True
Sheets("Menu").Select
End If
End Sub

Mike

 

[Tia]

Hi,

This assumes you have a sheet called 'Menu' that you use to Hyperlink to the
employee sheets. Right click the sheet tab of sheet 2, view code and paste
the code below in. Every time sheet 2 is now activated you are prompted for
the password before it becomes visible.

This is a solution for a single sheet and you could paste this into every
sheet with the name/password changed or get a bit more complex and use the
Workbook_SheetActivate event and build  generic code that works for every
sheet.

In practice I wouldn't do either. On the assumption you have employees of
reasonable capability any of them could crack this protection very easilly
and view other employee information.

Private Sub Worksheet_Activate()
Sheets("sheet2").Visible = False
response = InputBox("Enter Password", vbOKOnly)
If response = "MyPass" Then
    Application.EnableEvents = False
    Sheets("sheet2").Visible = True
    Sheets("sheet2").Select
    Application.EnableEvents = True
Else
    Sheets("sheet2").Visible = True
    Sheets("Menu").Select
End If
End Sub

Mike









- Show quoted text -

Actually i am using the following code to open the sheets
in the Main sheet
Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ws As Worksheet
Dim RngOfNames As Range
If Target.Count > 1 Then Exit Sub
If Range("D1").Value = "Tia" Then Exit Sub
Set RngOfNames = Union(Range("A8:A25"), Range("E8:E25"))
Application.ScreenUpdating = False
If Not Intersect(Target, RngOfNames) Is Nothing Then
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Main" Then ws.Visible = False
Next ws
On Error GoTo NoSht
Sheets(CStr(Target.Value)).Visible = True
Sheets(CStr(Target.Value)).Select
End If
Application.ScreenUpdating = True
Exit Sub
NoSht:
On Error GoTo 0
MsgBox "There is no sheet named " & Target.Value & ".", 16,
"Invalid Sheet Name"
End Sub

In the workbook
Private Sub Workbook_Open()
Dim ws As Worksheet
Sheets("Main").Select
If Range("D1").Value = "Tia" Then
For Each ws In ThisWorkbook.Worksheets
ws.Visible = True
Next ws
End If
End Sub

and i have tried the code that you gave me and i pressed on the name
of the sheet but i receive a debug message and this message is always
in yellow
Private Sub Worksheet_Activate()


Help

Tia