Limit Files via VBA not folder rights or permissions

[Guest]

Good morning,

Do you have any ideas as for securing a workbook based upon userid. I
believe each computer contains a API, MPR or DLL file which the current user
of that machines userid resides. I would like to be able limit access to
workbook based upon userid listed within the workbook.

Reason, the file contains sensitive information but needs to be accesible to
many. So, if someone who has rights to the folder saves the file and shares
it with someone else. That someone else will not have acces to the file with
out me specifying them somewhere in the VBA code or WB.

Is this possible?

 

[Tom Ogilvy]

Private Declare Function apiGetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" (ByVal lpBuffer As String, nsize As Long) As Long

Sub GetUserNameTest()
MsgBox fOSUserName
End Sub

Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If lngX <> 0 Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = ""
End If
End Function

 

[Guest]

If, I'm understanding the code corretly. This is obtaing the user name or
login, correct. How can I use this to limit access to specific users?

 

[Tom Ogilvy]

If you have the username from this code, compare it to your list of
authorized users. If it doesn't match, close the workbook. this would be
done in the workbook open event. Of course, this is easily defeated by
disabling macros.

Beyond that, you would need to approach your network administrators about
file permissions and so forth.

An alternative would be to store the data in a real database and use Excel
as a front end, passing in a username and password to retrieve authorized
data.

 

[Guest]

Tom,

I would first like to apologize for all the questions. I realtively new to
the VBA areana. Nevertheless, I have a list of the usernames already. How
would I get VBA to compare the list of authorized users and closethe workbook?

Disabling the macros, would render this feature useless. But, is it
possible to make the workbook run only with macros enable or close workbook?
Just thinking out load.

 

[Tom Ogilvy]

In a standard module (insert=>Module), put in code like this

Private Declare Function apiGetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" (ByVal lpBuffer As String, nsize As Long) As Long

Public Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If lngX <> 0 Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = ""
End If
End Function

In the VBE, in the project explorer, double click on the ThisWorkbook Entry
and in the ThisWorkbook Code module put in code like this:

Private Sub Workbook_Open()
Dim rng as Range
Dim sStr as String
Dim res as Variant
sStr = fOSUserName()
if sStr = "" then ThisWorkbook.Close Savechanges:=False
With ThisWorkbook.Worksheets("List")
set rng = .Range(.Range("A1"),.Range("A1").End(xldown))
End with
res = Application.Match(sStr,rng,0)
if iserror(res) then
ThisWorkbook.Close SaveChanges:=False
End if
End Sub


for insights on Events, see Chip Pearson's page on Events

http://www.cpearson.com/excel/events.htm

You can make it enticing for the user to enable macros, but you can't force
them too. If macros are disabled, it is like asking a stump to sing - you
have no control. You might actually have your data in a second password
protected, hidden workbook and have the user open a dummy workbook that
handles authorization - then opens the real workbook and closes itself. So
if the user diables macros in the dummy workbook, they don't have access to
the data.