Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Password required in VBA to open the sheet
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Password required in VBA to open the sheet
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" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Password required in VBA to open the sheet
On Oct 12, 1:09*pm, Mike H wrote:
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" wrote: 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- Hide quoted text - - 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Input password or open the sheet as "Read only | Excel Discussion (Misc queries) | |||
how do i change the message in the password required box | Excel Discussion (Misc queries) | |||
Password Required to View\edit a Sheet in a Workbook with Many She | Excel Discussion (Misc queries) | |||
How do I create a required password before opening an excel doc? | New Users to Excel | |||
Password required before Macro execution | Excel Discussion (Misc queries) |