CODEWITHSUNDEEP
excelvba
user3514236
user3514236

Reputation: 21

How can I open an excel file read-write for some users and readonly for others

I have an excel file that all my colleagues must have read access, but only a few may have write access.

I tried to introduce in workbook_open a procedure to test user and depending on it to decide how the file to be opened. I learned that does not work directly, so I tried to access an add-in that has a procedure that changes the readonly status.

Private Sub Workbook_Open()
    users = Environ("USERNAME")
    Select Case users
        Case "chris": MsgBox "ok"
        Case "david": MsgBox "ok"
        Case "sam": MsgBox "ok"            
        Case Else: Application.Run ("read_only")
    End Select
End Sub

This is the sub function in the Addin

Sub read_only()
   file_name = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
   Status = ActiveWorkbook.readonly

   ActiveWorkbook.Close ' (the problem is here because also this sub stops when my workbook closes)
   If Status = False Then
       Workbooks.Open fileName:=file_name, readonly:=True 
    Else
       Workbooks.Open fileName:=file_name, readonly:=False  
   End If
End Sub

Has anyone an idea how to solve this?

Upvotes: 2

Views: 2754

Answers (1)

brettdj
brettdj

Reputation: 55682

You can do it directly :)

Try this

Private Sub Workbook_Open()
Users = Environ("USERNAME")
    Select Case Users
        Case "chris": MsgBox "ok"
        Case "david": MsgBox "ok"
        Case "sam": MsgBox "ok"
        Case Else
        Application.DisplayAlerts = False
        On Error Resume Next
       'may already be read only
        If ThisWorkbook.Path <> vbNullString Then ThisWorkbook.ChangeFileAccess xlReadOnly
        On Error GoTo 0
        Application.DisplayAlerts = True
    End Select
End Sub

Upvotes: 1

Related Questions