Opening Workbooks as Read Only

[mcgrathm]

I want to open a workbook as read-only depending on which user is opening the file. I know how to test for the user but I cannot seem to get the syntax for Workbooks.Open quite right. Can anybody help me please

 

[Anne Troy]

Tough one. I would say that you will be better off password-protecting it for modifications. Hit File-Save as and choose Tools-General options. Put a password in to modify and give the password only to your selected users.

 

[dk]

Hi,

This can be done by placing some code in the Workbook_Open event code. Right click the lower of the 2 Excel icons at the top left of the Excel screen and choose View Code. Alternatvely, open the VB editor, click Ctrl R to open the project explorer. Then expand your workbook if it's not already expanded and double click the ThisWorkbook icon.

There are 2 names you could check. The first, and most unreliable is the UserName given by Excel. This is the one that you type into Tools, Options, General. For that, use this code:-

Private Sub Workbook_Open()
If Application.UserName <> "dk" Then
    Application.DisplayAlerts = False
    ThisWorkbook.ChangeFileAccess (xlReadOnly)
    Application.DisplayAlerts = True
End If
End Sub

The second method is to check the Windows logon name. For that use something like this:-

'GetUserName code is from here:-
'http://www.vbapi.com/ref/g/getusername.html

Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Private Function WindowsUserName()
' Display the name of the user currently logged on.
Dim username As String  ' receives name of the user
Dim slength As Long  ' length of the string
Dim retval As Long  ' return value

' Create room in the buffer to receive the returned string.
username = Space(255)  ' room for 255 characters
slength = 255  ' initialize the size of the string
' Get the user's name and display it.
retval = GetUserName(username, slength)  ' slength is now the length of the returned string
WindowsUserName = Left(username, slength - 1)  ' extract the returned info from the buffer
' (We subtracted one because we don't want the null character in the trimmed string.)

End Function

Private Sub Workbook_Open()
If WindowsUserName = "nothim" Or WindowsUserName = "nother" Then
    Application.DisplayAlerts = False
    ThisWorkbook.ChangeFileAccess xlReadOnly
    Application.DisplayAlerts = True
End If
End Sub

HTH,

Dan

 

[Ivan F Moala]

Dan....thats good......

Forgot all about the ChangeFileAccess bit
I was actually looking @ a diff method..