help with viewing specific sheets

[erenkey]

I need help with creating a login page. I want to create a login page that when an employee logs in it will only show their time sheet. All of my employees' time sheets are in the same workbook. I want to make it so that when an employee logs in, their sheet and only their sheet shows up, the only exception is is I log in, I then want all sheets to show up. can anyone help me? I have read some other posts but they did not make any sense to me.

 

[Jonmo1]

Start by hiding ALL sheets except 1. Best to use xlveryhidden.
Press ALT F11 to open VBA window
You'll find all the sheets listed in the top left, click view - properties window
select each sheet 1 by one and set the visible property to xlveryhidden.

The sheet you leave UNhidden should be named "login" or something, whatever...

close the VBA window.

Now right click the Excel Icon in top left corner next to "file - edit - etc" - click view code.

Paste the following.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("login").Visible = True
For Each Sheet In Sheets
    If Sheet.Name <> "login" Then Sheet.Visible = xlVeryHidden
Next Sheet
End Sub

Private Sub Workbook_Open()
Sheets("login").Visible = True

For Each Sheet In Sheets
    If Sheet.Name = Environ("username") And Sheet.Name <> "login" Then
        Sheet.Visible = True
    Else
        Sheet.Visible = xlVeryHidden
    End If
Next Sheet

If Environ("username") = "Master ID" Then ' Your Network Login ID goes here
    For Each Sheet In Sheets
        Sheet.Visible = True
    Next Sheet
End If
Sheets("login").Visible = xlVeryHidden
End Sub

Change the "Master ID" to whatever YOUR network logon ID is.

This depends on the employee sheet names being the same as their network logon ID's

 

[erenkey]

what do you mean by xlveryhidden?

I am very new to using VBA codes.

 

[erenkey]

What if I want to make the logins something different. I have a spread sheet that has all of my employees on it. Each employee has an employee number. i want to make the login their employee number. They don't need to enter their name just their number. Does this make a difference?

 

[Jonmo1]

xlveryhidden means that the sheet will be very hidden, so that you can't see it when going to format - sheets - unhide.

To set a sheet to very hidden, press ALT + F11 - that opens the VBA window. You'll see all the sheets listed on the top left. click view - properties window. highlight a sheet, in the properties window, find the line called Visible - set to xlveryhidden.


Do you have a table somwhere with say Employee #s in column A and respective sheetnames in column B? If so, what sheet is it on. If not, make one.

 

[erenkey]

I have a sheet called Employees. In column A is their Employee Number, Column B is their Name. Is this all of the information that you need?

 

[Jonmo1]

and the sheets are named by the employee NAME?

 

[Jonmo1]

If on Sheet named Employees
column A = Employee IDs
column B = Names

and Each sheet is Named same as column B THEN this should do.

Right click the excel Icon in top left corner next to "File,Edit,etc" View Code

And you have a sheet named "login" that will be visible upon opening AND/OR Closing the workbook. You can name that anything you want, just change login anywhere in the code below to the name of any sheet that can be visible to anyone.

Paste the following

Private Sub Workbook_BeforeClose(Cancel As Boolean) 
Sheets("login").Visible = True 
For Each Sheet In Sheets 
    If Sheet.Name <> "login" Then Sheet.Visible = xlVeryHidden 
Next Sheet 
End Sub 

Private Sub Workbook_Open() 
Sheets("login").Visible = True 
1:
On Error Goto 1
x = Inputbox("Please Enter your Employee Number")
x = worksheetfunction.vlookup(x,Sheets("Employees").Range("A:B"),2,False)

For Each Sheet In Sheets 
    If Sheet.Name = x Then 
        Sheet.Visible = True 
    Else 
        If Sheet.Name <> "login" Then Sheet.Visible = xlVeryHidden 
    End If 
Next Sheet 

If x = "YOUR Employee Number Then ' Your Employee ID goes here 
    For Each Sheet In Sheets 
        Sheet.Visible = True 
    Next Sheet 
End If 
Sheets("login").Visible = xlVeryHidden 
End Sub