Simple Username Password Help

[SalfordLad06]

Help! I've searched a few message threads but can't find anything that has helped so far.

I have a shared workbook located on a network. The workbook has multiple worksheets containing private information about employees' Annual Leave and sickness etc. To access the network, we each have to log into windows using our usernames and passwords. I have tried in vain to write some simple vba code that allow managers (identified by their username) to view all worksheets, yet only allow staff to view their own particular worksheet using If Then Else in the This Workbook in Project Explorer.

I am getting lost in lines and lines of personalised code for each user, and I know instinctively that there must be an easier way to resolve this.

ANY HELP GREATLY APPRECIATED! Thanks

 

[hatman]

Milo, you are absolutely right. My interpretation of "Simple" is to prevent a user from casually stumbling into places he doesn't belong. Possibly to prevent a user from getting curious and "looking under the hood" and going through the motions of "I wonder what would happen if I did THIS."

Your latest post, at least in my mind, is an example of someone with intent to crack your security. I like to think that someone with that level of knowledge will excercise retraint, or will only utilize such a "back door" under appropriate circumstances. Besides, they also have either a)know the structure of your security well enough to try that avenue, or b)be bored or determined enough to try that method as part of a trial and error process.

The thing about security is that NOTHING is undefeatable... it's simply a question of making it difficult enough to prevent a certain percentage of the population from accessing what you don't want them to see. The questions become 1)Who is going to try to acces this info, 2)Why are they trying to acces it and 3)What skills will they potentially have. The answers you find will determine if what we have discussed is sufficient, or if you need to go to the next level of time and energy... I honestly woudn't know how to take that step, myself, because I have never considered what is beyond the simple steps we have already covered.

 

[Joe Was]

First use this code to poll your network:

Sub mySys()
Dim DomainName, ComputerName, UserName
DomainName = Environ("UserDomain")
ComputerName = Environ("ComputerName")
UserName = Environ("UserName")

MsgBox "Network Domain Name: ==> " & DomainName & Chr(10) & _
"Full Comuter Name: ==> " & ComputerName & Chr(10) & _
"User Name: ==> " & UserName & Chr(10) & _
Chr(10) & "Application User Name: " & Application.UserName
End Sub

This is a simple list of the system ID Info. this will let you see what kind and what format that info has!

Then you can pick the right info to test with, in this next test I used: "Application.UserName" to work with you may need to change this to one of the others returned by the 1st Sub above?

Sub myUser()
'Show User Name!
If Application.UserName = "" Then GoTo NoNam Else GoTo HNam
End

NoNam:
'User name not in system.
'Get User Name as input.
UNText = Application.InputBox( _
prompt:="The user: " & UserName & " has not indicated their name," & Chr(13) & _
"in the Tools-Options-General, User Name: box of Excel." & _
Chr(13) & "Or the network system User Name was not found?" & _
Chr(13) & Chr(13) & _
"Please enter the User Name here:" & Chr(13) & Chr(13) & _
"Note: This will not change the User Name in Options!", _
Title:="User Name not installed on this PC!")

HNam:
MsgBox "Environment User Name: " & Environ("username") & Chr(13) & _
Chr(13) & "Application User Name: " & Application.UserName
End Sub

This code checks to see if the users ID is in the system, more correctly it checks to see if the PC being used has been registered!

Once you get which ID to use as your KEY worked out, Add code like the "myUser" Sub above to a: ThisWorkbook module Open Event with a "Select Case" structure to control which sheets that user can view:


Public thisUserN$
'Note: the Public Var above should be the
'1st entery at the top of the
'ThisWorkbook module!

Private Sub Workbook_Open()
'ThisWorkbook module code!
'Show User Name!
Dim sh As Worksheet

If Application.UserName = "" Then GoTo NoNam Else GoTo HNam
End

NoNam:
'User name not in system.
'Get User Name as input.
UNText = Application.InputBox( _
prompt:="The user: " & UserName & " has not indicated their name," & Chr(13) & _
"in the Tools-Options-General, User Name: box of Excel." & _
Chr(13) & "Or the network system User Name was not found?" & _
Chr(13) & Chr(13) & _
"Please enter the User Name here:" & Chr(13) & Chr(13) & _
"Note: This will not change the User Name in Options!", _
Title:="User Name not installed on this PC!")

HNam:
thisUserN = Application.UserName

Select Case thisUserN

Case "JoeWas"
'Authorize user: JoeWas for Sheet2 only!
For Each sh In Worksheets
If sh.Name <> "Sheet2" Then
sh.Visible = False
Else
sh.Visible = True
End If
Next sh

Case "NextUsersID"
'Authorize user: NextUsersID for Sheet3 only!
For Each sh In Worksheets
If sh.Name <> "Sheet3" Then
sh.Visible = False
Else
sh.Visible = True
End If
Next sh

Case "xxxxxx"
'Authorize user: BackDoor code xxxxxx for Sheet4 only!
For Each sh In Worksheets
If sh.Name <> "Sheet4" Then
sh.Visible = False
Else
sh.Visible = True
End If
Next sh

Case Else
'Authorize user: Not Found! for No sheets!
For Each sh In Worksheets
If sh.Name <> "Sheet1" Then
sh.Visible = False
Else
sh.Visible = True
End If
Next sh
MsgBox "You are not authorized to use this application!"

'Option: Exit Application, un-comment below!
'Application.Quit
End Select
End Sub


Just add as many Sheets and Case Statements as you need, and you are done!
Note: The Public Var: thisUserN can be used by any Sub in any Module as needed!

Now lets say you have 10,000 users, the Select Case structure will not be practical so don't use the Select Case Structure: Name each Users Sheet the name returned by the system!

And, only use:

For Each sh In Worksheets
If sh.Name <> thisUserN Then
sh.Visible = False
Else
sh.Visible = True
End If
Next sh


So, this one code block will work for every registered User!
As in this case our Public Var: thisUserN = Application.UserName
Which also happens to be the name of that users Sheet!

You can modify the code to also include Sheets everyone can view by changing:

If sh.Name <> thisUserN Then

To:

If (sh.Name <> "Sheet1" Or sh.Name <> thisUserN) Then

Now lets say you have all you users on one WorkSheet?

Name each users data range with a Range Name then Range Name the "common to all" range(s). Change the code to: Hide all but the indicated Ranges!

To protect your users from getting into your code and seeing what you used to keep them out, see this link:

http://www.mrexcel.com/board2/viewtopic.php?t=172763

 

[Milo_Minderbinder]

The thing about security is that NOTHING is undefeatable... it's simply a question of making it difficult enough to prevent a certain percentage of the population from accessing what you don't want them to see.

That's why I think a combination of my 1st and 2nd posts is a good idea. If someone runs the code in my 3rd post, they still have to contend with the "security" of my first post. Sure they'll be able to see that other sheets exist, they just won't be able to access them without even greater difficulty.

Joe Was post is interesting (I really like the idea about havign sheet names = usernames). Maybe not a problem for the OP, but depending on the requirements, his code might require a lot of customized coding, likely to require regular updates.

I thought about a scenario where upon opening the source file, code would copy the sheets that user has rights to to a new workbook and immediately close the source workbook after the copy. It would allow read access to sheets without the ability to edit the info or mess around with an opened source (not open source ) file. Of course the file admin would be able to keep the source file open.

 

[Joe Was]

This code will automatically select the sheet that matches the users name.
Only Sheet1 and the sheet with the same name as the user will be displayed!

Note: ToolBar: Format - Sheet - UnHide will display any sheet!
We could add code to deactivate that menu option and gray it out on the menu, though.

The problems with the code below, a Sheet Name must match the User Name or an "your not authorized" mssage pops up, you need to add additional code [Select Case] to filter between all access or selected access [as in my other post (adds maintenance and undermines the automatic aspect of the code)], as the code is now each user must be on their own PC.

Note: Layers of security are always best.


Public thisUserN$
'Note: the Public Var above should be the
'1st entery at the top of the
'ThisWorkbook module!

Private Sub Workbook_Open()
'ThisWorkbook module code!
'Show User Name!
Dim sh As Worksheet

If Application.UserName = "" Then GoTo NoNam Else GoTo HNam
End

NoNam:
'User name not in system.
'Get User Name as input.
UNText = Application.InputBox( _
prompt:="The user: " & UserName & " has not indicated their name," & Chr(13) & _
"in the Tools-Options-General, User Name: box of Excel." & _
Chr(13) & "Or the network system User Name was not found?" & _
Chr(13) & Chr(13) & _
"Please enter the User Name here:" & Chr(13) & Chr(13) & _
"Note: This will not change the User Name in Options!", _
Title:="User Name not installed on this PC!")

HNam:
thisUserN = Application.UserName

'On Error GoTo myEnd

For Each sh In Worksheets
If sh.Name = thisUserN Then n = n + 1

If (sh.Name = "Sheet1" Or sh.Name = thisUserN) Then
sh.Visible = True
Else
sh.Visible = False
End If
Next sh

If Not n > 0 Then MsgBox "You are not authorized for other sheets!"
If n > 0 Then Sheets(thisUserN).Select

'Option: Exit Application, un-comment below!
'Application.Quit

myEnd:
End Sub

 

[SalfordLad06]

Wow! Thank you very much for all your help and advice - I am impressed! I will work my way through the options and get back to you. I am actually dealing with about 15 users, none of whom are particularly computer literate - they think macros are an Italian sidedish! The Security aspect is therefore quite low on the agenda, I just wanted to prevent them peeking at others users' personal info in the workbook. Thanks again!

 

[Joe Was]

You may also want to look at this:

http://www.mrexcel.com/board2/viewtopic.php?t=166587

It shows how to gray a menu item and how to make subs private.