Opening Workbooks as Read Only
Find bottlenecks in your Excel workbooks
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Opening Workbooks as Read Only

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,581
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    ~Anne Troy

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,936
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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:-

    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:-

    Code:
    '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



  4. #4
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

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


    Kind Regards,
    Ivan F Moala From the City of Sails

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com