Open sheet based on Username

[mrsviqt]

I have a workbook with 6 worksheets. Titled Adults, Kids, Total, Kids 2,
Total Kids, and Total 2. There are certain cells on Adults, Kids, and Kids 2
that need to be filled in. Using Tools->Protection->Allow Users to Edit
Ranges, I have made it so only certain people can edit certain cells without
a password. I have also made all cells of all sheets open to two people
(myself and my boss), so that we may edit without having to enter a password.

What is need is a macro that grabs the username and then only allows that
person to see a certain sheet.
Example:
Joe Smith and John Doe can view sheets Adults and Total
Joan Smith and Jane Doe can view sheets Kids, Kids 2, Total, and Total Kids.
Myself and my boss can view all sheets.

If necessary, I can make them enter a password (which I would just make
their username).

Is this even possible?

Thanks in advance for any assistance provided.

 

[Nelson]

Hi mrsviqt, Try below, but you need to:
1) Creat a new sheet name as Cover_Page or other name.
2) Use xlveryHidden (the first section of the for next loop) to hide all
sheets during workbook save so that the moment workbook open all the sheets
already hide.

Sub Macro1()
Dim Message, Title, Default, MyValue
For Each sh In Sheets
If sh.Name = "Cover_Page" Then
Else:
sh.Visible = xlVeryHidden
End If
Next sh

Message = "Enter a username, case sensitive" ' Set prompt.
Title = "InputBox Demo" ' Set title.
MyValue = InputBox(Message, Title, Default)
If MyValue = "Joe Smith" Or MyValue = "John Doe" Then
Sheets("Adults").Visible = True
Sheets("Total").Visible = True
ElseIf MyValue = "Joan Smith" Or MyValue = "Jane Doe" Then
Sheets("Kids").Visible = True
Sheets("Kids 2").Visible = True
Sheets("Total").Visible = True
Sheets("Total kids").Visible = True
ElseIf MyValue = "Myself" Or MyValue = "my boss" Then
For Each sh In Sheets
sh.Visible = True
Next sh
End If
End Sub

Hope the above help you

 

[mrsviqt]

That works great. But I just thought of something else. Can we make it so
the username has to equal something, and the username that the system
pullshas to be correct? This will prevent Joe Smith from entering Jane Doe's
username?
Thanks.

 

[Dave Peterson]

If that information cannot be shared or viewed by others, then don't put it into
an excel workbook.

If you have to, then don't share it with others.

Excel's worksheet and workbook protection is easily broken. It's designed to
keep people from writing over formulas (and the like). It's not meant to
protect information like this.

 

[jaf]

Hi Nelson,
A simple edit.


Sub Macro1()
Dim Message, Title, Default, MyValue
For Each sh In Sheets
If sh.Name = "Cover_Page" Then
Else:
sh.Visible = xlVeryHidden
End If
Next sh


'Message = "Enter a username, case sensitive" ' Set prompt.
'Title = "InputBox Demo" ' Set title.
'MyValue = InputBox(Message, Title, Default)

MyValue = Application.UserName 'XXXXXXXXXXXXXXX

If MyValue = "Joe Smith" Or MyValue = "John Doe" Then
Sheets("Adults").Visible = True
Sheets("Total").Visible = True
ElseIf MyValue = "Joan Smith" Or MyValue = "Jane Doe" Then
Sheets("Kids").Visible = True
Sheets("Kids 2").Visible = True
Sheets("Total").Visible = True
Sheets("Total kids").Visible = True
ElseIf MyValue = "Myself" Or MyValue = "my boss" Then
For Each sh In Sheets
sh.Visible = True
Next sh
End If
End Sub

John