Using Environ$("username") as a security measure

[Scoob55k]

Good afternoon,

I am working in an Excel workbook, of which should maybe be an Access DB, but my supervisor started with Excel and I'm helping him see how far we can push the envelope in Excel. I'm not sure he knows this.

I have used the term Environ$("username") in Access to secure and allow only users with the proper access to a form/report, etc. by using an IF, THEN, ELSE statement which checks their logged on USERNAME before going to the form/report, etc.

I have an Autpen macro which populates cell B1 on the Main worksheet with the users "username". On the Main worksheet I have buttons that go to other pages in the workbook. By using the code below I want to restrict users depending on their "username" from going to certain worksheets. Sure this uses the term "security" lightly, but I feel it will get the job done and with other measures I'm taking, should serve the purpose. Let me know what you got. Much appreciated!
----------------------------------------------------------
Sub Button3_Click()
' Dim myRange As Range
' Set myRange = Worksheets("Main").Range("B1")
If Range("B1").Value = "SI5K" Then
Sheets("Beth").Select
Else
MsgBox "You do not have access to this page", vbCritical, "Access Denied"
End If
End Sub

 

[PHV]

Why not simply this in your Autpen macro ?
ThisWorkbook.Sheets("Beth").Visible = IIf(Environ("USERNAME") = "SI5K", xlSheetVisible, xlSheetVeryHidden)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[HughLerwill]

I'm not quite sure what your question is? Do you want to hide the Sheets which a user is not supposed to use or what?

A side issue.
Use of Environ$ to return such information is sometimes frowned upon because it can be unreliable and relatively easy for a user to 'fake' if they have access to a command line interface.
The following code may be of interest.

'return network user name
Public Declare Function WNetGetUser Lib "mpr.dll" Alias "WNetGetUserA" (ByVal lpName As String, ByVal lpUserName As String, lpnLength As Long) As Long

Public Function NetUserName$()

Dim User$, nLen&, lret&

NetUserName$ = "No LAN"
nLen = 255
User$ = String$(nLen, 0)
lret = WNetGetUser(vbNullString, User, nLen)
If lret = 0 Then NetUserName$ = Left$(User$, InStr(User, Chr$(0)) - 1)

End Function

regards Hugh

 

[Scoob55k]

Hey, that looks good my friend. Just a few questions:

My workbook name is "CA&P Compiled Scorecard 2007.xls"

- Would I use "ThisWorkbook", "xlSheetVisible", and "xlSheetVerryHidden" in my code or are these generic "for example" statements?

- What exactly is the ", xlSheetVisible, xlSheetVeryHidden" portion of the for or do?

Thanks PH!

Clayton

 

[PHV]

ThisWorkbook is the workbook from where the macro runs.
xlSheetXXX are global Excel constants.

Tip: when in the VBE feel free to play with the F2 and F1 keys ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Scoob55k]

Hugh,

I will need more explaination of the code you used. I am by no means a proficient "writer" of VBA code. I simply modify it by use of "recorded macros" and such. I do have an "Access 2002 VBA Handbook" though. Maybe that will help?

Thanks

 

[Scoob55k]

So PH,

Just to be certain, change the "ThisWorkbook" to my workbook name (surrounded by brackets I assume due to spaces in the name) and leave the xlSheetXXX comments as they are? If this is correct, no response needed and either way thanks so much for your time and knowledge.

Clayton

 

[TheComputerDude]

You could try the following code on the ThisWorkbook module in Excel:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name <> "Sheet1" Then
If Not ValidSheetUser(Sh.Name) Then
MsgBox "You are not authorized to view this sheet", vbOKOnly + vbCritical, "Unauthorized Sheet"
Worksheets("Sheet1").Activate
End If
End If
End Sub

Private Function ValidSheetUser(SheetName As String) As Boolean
Dim strUserName As String
Dim strUserString As String
Dim intPosition As Integer

strUserName = UCase(Environ("UserName"))
Select Case SheetName
Case "Sheet2":
strUserString = "BOB|MARY|JAMES|GEORGE"
Case "Sheet3":
strUserString = "MARY|GEORGE"
End Select

ValidSheetUser = False
intPosition = 1
Do While intPosition > 0
intPosition = InStr(intPosition, strUserString, "|", vbTextCompare)
If intPosition > 0 Then
If strUserName = Left(strUserString, intPosition - 1) Then
ValidSheetUser = True
Exit Do
End If
strUserString = Right(strUserString, Len(strUserString) - intPosition)
Else
If strUserName = strUserString Then
ValidSheetUser = True
End If
End If
Loop
End Function

 

[PHV]

If your macro is in CA&P Compiled Scorecard 2007.xls, then change nothing, otherwise:
Workbooks("CA&P Compiled Scorecard 2007.xls").Sheets("Beth").Visible = IIf(Environ("USERNAME") = "SI5K", xlSheetVisible, xlSheetVeryHidden)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[HughLerwill]

Scoob55k,

Get PHV's code working and then come back.

Provide the final working code for your Autpen macro.

regards Hugh,

 

[Scoob55k]

OK,

It looks as if the major problem I am having is with the Environ("UserName") being reconized. In all the If/Then/True statements it is running the false part.

If I get anything out of this that will be all worth it for future projects. I still plan to check out the others as well. I like the idea of your code you gave me PHV. It runs the false part of the IIF statement though. At first I didn't think it was working at all, but when I went to unhide the "Beth" sheet I noticed it wasn't there and the xlVeryHidden command was just that, "VeryHidden". Also like the F1 and F2 tip. Thanks!

I can get it to put my UserName in cell B1 by using:

Sheets("Main").Select
Range("B1") = Environ$("UserName")

in the AutoOpen Macro, but I had the same problem I am having now when I used that before in my code. Not sure the importance of the $ sign either as you did not have it in your code, but either way, the sheet is "VeryHidden".

I know I am missing something here. I apologize for my ignorance. Thanks for you help everyone.

 

[Scoob55k]

I got this to work:

ThisWorkbook.Sheets("Beth").Visible = IIf(Range("B1") = "si5k", xlSheetVisible, xlSheetVeryHidden)

using the

Range("B1") = Environ$("UserName")

code in my Autpen macro. Still working at it. Thanks!

 

[mrmovie]

dont do anything based on username otherwise you ahve to update you code for new users etc. do it on Active Dir groups (if you have them of course) and work out group membership instead

 

[HughLerwill]

Your latest example includes "si5k", your previos examples used "SI5K"

A case of case?

 

[Scoob55k]

HughLerwill,

Looks to be. I guess it's all good as I go some very good information for this and future projects. I'll get back to you on your code when I get the chance. I think I'll get it to work then get back to you so I can upgrade the file. I always like to go best case long term. Thanks!

 

[PHV]

So, what about this ?
ThisWorkbook.Sheets("Beth").Visible = IIf(LCase(Environ("USERNAME")) = "si5k", xlSheetVisible, xlSheetVeryHidden)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Scoob55k]

Actually I got it to work once I used "si5k" in place of "SI5K". Thanks so much for your help!