MrExcel Publishing
Your One Stop for Excel Tips & Solutions

user level security in Excel file


Posted by rebecca on February 12, 2002 5:52 PM

Is it possible to build a login screen for an excel file? I would like to have three different login's for an excel file that is being used for budgeting. THis way all three users could have a different level of security if possible, i.e. some cells locked for one user and other cells locked for another user.


Posted by DK on February 13, 2002 5:17 AM

You can, but it will probably involve a fair amount of coding. Rather than use a login screen I check the Windows username. If it's a valid name then I make the necessary changes through code e.g. which sheets they can see, if they can update cells, etc. This is the code I use for one a cost centre report used in my company where different users can see different things...

In the Workbook_Open event procedure:-

Private Sub Workbook_Open()
Application.EnableCancelKey = xlDisabled
SetUserPriveleges
End Sub

In a standard module something like this. I've changed the code a bit to protect the innocent :-)

Option Private Module
Option Compare Text
Option Explicit

Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" _
(ByVal lpBuffer As String, nSize As Long) As Long
Sub SetUserPriveleges()
Select Case Username
Case "dsmith"
'Do stuff for D Smith
Case "dk"
'Unprotect everything
Case "averageidiotuser"
'Ensure maximum protection, issue warnings all of the time,
'don't let this person do anything, even make coffee.
Case Else
MsgBox "You can't view this workbook. If you should be able to then please contact DK", vbExclamation, "Error!"
ThisWorkbook.Close False
End Select

End Sub

Function Username() As String
Dim strUserName As String
Dim slength As Long
Dim retval As Long

strUserName = Space(255)
slength = 255
retval = GetUserName(strUserName, slength)
strUserName = Left(strUserName, slength - 1)
Username = strUserName
End Function

You would also need to allow for the fact that someone may open the workbook and disable macros. The usual way of getting around this is to have a sheet which says something like 'Please enable macros' which is the only sheet on view when the workbook is open. If macros are enabled then have code to run to hide this sheet and unhide all of your other sheets. Just before the workbook is closed, unhide the 'Please enable macros' sheet and hide everything else with xlSheetVeryHidden.

Hope this makes sense


Posted by rebecca on February 13, 2002 11:49 AM

Very good advice. I solved through coding. As indicated by you coding was very complex, including identifying each range of cells that need to be either locked or unlocked by viewer of data. Thanks.